Replace certain characters

Hi all

I'm trying to work something out, I'm afraid I don't have a clue with this. My problem is that I'm going to start receiving spreadsheets containing letters that an end system won't understand (actually due to font constraints). For example, an é will not display. Hence I am going to replace all é characters with e.

Usually I'd do a CTRL+F, and 'replace all' but I'm trying to design a one-click solution. My plan is:

The first spreadsheet contains a macro and a reference table. Column A of the reference table has all known "bad" characters. Column B has the characters that will replace the bad characters. This table will evolve over time, I.E. it will grow larger or smaller, as we discover more characters that don't work. The macro in this sheet will look at a second sheet, the subject sheet, and find all the instances of the first character in column A of the reference table. Then it will replace those instances with the first character from column B. Then it will automatically move on to the next character, and so on.

I'm pitiful with macros, can anyone help me with this. It will save me many many many hours.

Thanks in advance

BB

Hi,

See this sheet attached. In Sheet1, Column 1 enter your list of characters to change from row 2 onwards. In Sheet1, Column2 enter your list of characters to change to from row 2 onwards. Then run the macro with ctrl>shift>r.

Note there is a dependence on the sheet names, so be careful to change the code if you change these names. For reference the code is:

Please Login or Register  to view this content.