Hi,
I'm collecting postcodes from a website then importing them into Excel for analysis.
For reasons beyond my control, 5, 6 and 7 digit postcodes are being submitted in one of the following formats;
N1 1AA
N11AA
N11 1AA
N111AA
NN1 1AA
NN11AA
NN11 1AA
NN111AA
I need a formula that can 'analyse' these different formats and create a new result that shows only the prefix.
I guess the way it would work would be to first remove (or ignore) any spaces. This would then result in all the above variations being shown as either;
N11AA
N111AA
NN11AA
NN111AA
It would then need to figure out whether to return a result of 3 or 4 characters. In other words, the above results would then be shortened in the following way.
N11AA would become N1
N111AA would become N11
NN11AA would become NN1
NN111AA would become NN11
So, if you look at each possible variation, you'd get the following for each.
N1 1 AA would become N1
N11AA would become N1
N11 1AA would become N1
N111AA would become N11
NN1 1AA would become N1
NN11AA would become NN1
NN11 1AA would become N1
NN111AA would become NN11
Can anyone tell me a formula (or formulas) that can achieve this?
Many (many) thanks,
Dom
Bookmarks