Muti find & replace function

Hi,

Is there an easy way to find and replace text in a spreadsheet using a formula.

For example I have the word Japan which is shown approx 12 times in various places in a large table C1:K500, I would like to replace the word Japan with Japan ABC 1000.

I have 30 other country's that also require a new name in the same sheet.

I can obviously use Ctrl H, but this would take a long time. Is there a formula I could use from a look up table, i.e find in sheet C1:K500 the word "Japan" which is in cell A1 and replace with the new word in B1 2Japan ABC 1000"

Appreciate any help.

No; it would require VBA.

In the alternative, you could put formulas in all those cells that make reference to the single cell in which Japan or Japan ABC 1000 appears.

I guess I'm confused why you ask in the programming forum if something can specifically be solved without VBA, there are many other forums here for non-vba stuff, why go after it here?

Anyway, if a VBA solution is what you actually want...

1) Somewhere in your workbook create a table of search/replace strings

Please Login or Register  to view this content.

2) Post up your sample workbook with that table filled out.
Click GO ADVANCED and use the paperclip icon to post up your workbook.

We can probably help with that from there.

Japan is in the list twice. Please advise. Are rows 2,3 and 4 supposed to be there?

I deleted the entries from rows 2:4, then ran this, seems to do it:

Please Login or Register  to view this content.