All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
How to search and replace text in bulk?
nqservices
Member
Hi everyone,
I know this is not directly related with hosting, but I really need help to on this. So I hope people don’t mind for me to post this here and if you can help, it would be great!
I have a client that gave me one list in Excel with their quotes from past years and now I have to organize it by renaming some values on the list as the example I leave bellow:
List Example:
...
2014-22-06-DEP-SAC-IX-123456
2014-22-06-DEP-SEC-IX-123457
2014-22-06-DEP-SUC-IX-123458
...
What I need to do is to find and replace all values like for example “SAC” to “NEW-SAC”.
This is easy to do with Excel or even the Windows Text Editor….for one or a few terms. But the problem is that this are huge lists and I have to find a replace almost 500 terms.
Making it manually one by one in Excel will take many hours and this is something I will need to do to other clients in the future.
What I need is a tool or a way where I can add multiple terms in bulk and then replace all then at once.
So does anyone knows a way to do this in a fast way? Any software, online tool, etc..?
Thanks
Comments
Do you have the old and replacement values in the form of lists?
Throw them on a linux box, run:
sed -i -e 's/SAC/NEW-SAC/g' *Will do the trick for replacing strings easily
Go with String-O-Matic. It's so fxxking great software.
I have the full original list and then other list with just the names/fields to change.
If it's not a ton of different types of replacements you need to do, you could use the search and replace function (CTRL+H).
Why not use Find/Replace on Excel? 500 is less.
You could write a simple macro in Excel, you know. They are fairly easy to do.
A quick web search yielded these discussions that should help:
http://stackoverflow.com/questions/23408568/find-and-replace-multiple-words-in-word-from-excel-list
Or use Visual Basic (comes with Office):
https://www.thespreadsheetguru.com/the-code-vault/2014/4/14/find-and-replace-all
http://superuser.com/questions/620484/find-and-replace-multiple-values-within-selected-cells
I don't get it. Why you not just use simple find/replace feature in Excel as other suggested?
Because that way I must find/replace one word in a individual way.. and will take to much time to find/replace 500 words.
Hope waiting your answer here wont take much longer than find/replace 500 words by excel
Turn the spreadsheet into a csv file.
Make a new csv file with 2 columns : "old word" and "new word".
Make a python or PHP script to loop the first csv file and search/replace using the second csv file.
If you still need help, PM the files.
Thanks all for your suggestions. Problem is solved!