Find/Replace macro with lookup table of change from to change to

[Gti182]

Hi All,

I'm having an annoying issue when running reports from our financial system. Some account/analysis codes which are 3 or 4 character text are reformatted to numbers or dates when exported from the system to excel.

Example:
Analysis code 6E2 is reformatted to the number 600 and Account code JAN5 is reformatted as date 36526 or 01/01/2000

I basically have to find and replace all the account/analysis errors manually by filtering the data set.

I've attached an example of an export with errors appearing in columns E & H only and highlighted the errors in yellow.

36526 or 01/01/2000 change to JAN0
37257 or 01/01/2002 change to JAN2
38353 or 01/01/2005 change to JAN5
38718 or 01/01/2006 change to JAN6
39083 or 01/01/2007 change to JAN7
39448 or 01/01/2008 change to JAN8
6 or 6.00E+00 change to 6E0
600 or 6.00E+02 change to 6E2
60000 or 6.00E+04 change to 6E4
7 or 7.00E+00 change to 7E0