I have a spreadsheet which will routinely contain text that I would like to automatically replace, but there are so many that a manual find/replace is no time savings. What I am trying to accomplish is to call some sort of lookup from Excel that will replace "text A" with "text B" every time it sees it, but then go down the list and also replace "text C" with "text D" and so on. I have about 160 pieces of text that will be replaced with new text. The replacements never change - text A will always be replaced with text B; it is pre-defined.
For example, I have a cell with the paragraph: "Jeff and Sandy have 45 apples from the grocery store."
and I want to call a function that will always replace every instance of "Jeff" with "Jeffrey", "Sandy" with "Sandra" and "grocery store" with "Kroger".
I could literally write a macro of doing a find and replace for every 160 pieces of text, but I would rather have some sort of lookup. Whether in an Access DB or another array of cells; I'm just not sure what the best route is or where to start. I have some shell scripting knowledge and if this were Linux I would just create a flat file and reference it on a for loop - I'm kind of looking for a way to do the same thing somewhere in Office.
One issue is also that I am limited on what I am permitted to do as a non-administrative user and also have very limited vbscript experience. Ideally I'd like it to be sophisticated enough to work well and save us time, but simple enough that anyone with a little training could support it.
I've be grateful for some ideas or guidance on best routes to accomplish this!
Bookmarks