Formula Needed To Truncate Postcodes

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

assuming your data is in A1, down, use this, copied down...
=LEFT(SUBSTITUTE(A1," ",""),LEN(SUBSTITUTE(A1," ",""))-3)

Hi Dom,

I think you've nailed most of your answer in the post. The only bit missing I think is the exclusion of the last 3 characters from the text without spaces. Steps as folls:

Original: NN11 1AA
Remove spaces: NN111AA
Length of modified text: 7
Characters to remove (1AA): 3
Final isolation: LEFT("NN111AAA",7-3)

The attached workbook breaks down the formulas for each of the steps and has one one formula that combines all the steps. Hope this helps.

best regards, dims

Postcodes.xlsx

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Another shorter way..

Please Login or Register  to view this content.