Extract Post Code data from a free text field

[DGillham]

Good Afternoon,

Just to provide a little background, I receive from a client a txt file which contains information relating to stock. This file is then loaded in Excel so manual changes can be made.

However, the field which identifies the location of the stock is a free text field, hence the format of the data entry can vary and I don't believe we can get this altered.. I need to be able to extract the UK post code from this field, which if this field was in a regular format would not be so bad, but the Post Code can be anywhere in this field. E.g.

BA113R Example Site 1
Example Site 2 BA60RR
Example EX456RT Site 3

Talking to a colleague he has suggested that as the Post Code is the only entry with numbers we try to get Excel to identify all of the characters which numerical values, remember them, and then copy with them the two characters either side i.e the whole post code.

The problem therefore is how? My colleague can programme everything apart from formulas/macros in Excel so it's a great idea but I was hoping someone maybe able to give us a leg up in terms of achieving it.

Thanks in advance.

Darren

[martindwilson]

some uk postcodes don't follow that format tho
see
http://en.wikipedia.org/wiki/Postcod...United_Kingdom

[pike]

Hi DGillham
try this UDF

Please Login or Register  to view this content.

[pike]

Didnt think it was that simple
the pattern should be

Please Login or Register  to view this content.

[martindwilson]

as long as there are absolutely no other numbers eg site 1 !
then this should cover it as long as the codes are separated by space
it covers all the formats of post codes (i think) and allows for no spaces when there should be

[DGillham]

Thank you both ever so much - Monday morning at work seems good for once!!

Best regards

Darren