Hello

I'm currently dealing with a number of Excel databases containing rows of data with the main identifier being the reference number in the first column. For some annoying reason these references are modern variations of historic reference numbers we have and for this project I need the historic numbers which we do not have readily available so I'm trying to convert them intelligently.



We have a set manual criteria for converting these modern references. The modern references are always formatted in the same way - four letters, a full stop and then four numbers. All I need to convert are the letters at the beginning based on a set criteria.

I'm trying to come up with a way of converting these automatically, probably with a Macro, but I'm struggling to figure out how to write it. I need the macro to loop through all the modern reference numbers in the column and replace the four letters with the set criteria, for example 'CTCP' (modern ref) would convert to 'P '.

I've probably not explained it too well, but I hope someone can help. I've attached a mock up of one of the spreadsheets if it helps.

Thanks.

Attached Files

Example conversion.xls (16.5 KB, 11 views)