Splitting and extracting certain info

[Greg_Norman]

Hello everyone,

I have a spreadsheet which contains addresses split randomly into several columns and need to extract the postcode for every row.

WEST STREET CF34 9AF MAESTEG
DERWEN CLOSE SA5 4QQ SWANSEA
GOETRE BELLAF ROAD SA2 7RL SWANSEA
TALIESIN CLOSE BRIDGEND CF35 6JR
TALIESIN CLOSE CF35 6JR BRIDGEEND


Above is a small example of 2 of the columns, most addresses are in 3 columns.

The postcode can appear at the front, end or anywhere in the address field. Depends on who typed it in. I need to somehow extract the postcode and have it in a separate column.

Text to columns wont work i think because the info isnt uniform, I've tried the search for the various postcode starting letters but still cannot extract the entire postcode.

Any help would be greatly appreciated.

Thank You

Greg

[DonkeyOte]

Greg, what you want to do is as you say relatively complex... I would personally either opt for a UDF using Regular Expressions or alternatively install Laurent Longre's morefunc.xll which has certain RegEx functions you can use, eg:

If we say your strings are in A1:C1 the below should return the Postcode to D1

D1: =REGEX.MID(A1&B1&C1,"([a-z]{2}[0-9]+\s[0-9][a-z]{2})",,FALSE)

Fore more info. on morefunc: http://xcell05.free.fr/morefunc/english/
For download see: http://download.cnet.com/Morefunc/30...-10423159.html

EDIT: In retrospect if you have London postcodes you should revise the first [a-z]{2} to [a-z]{1,2} given W12 etc... not all postcodes will necessarily have 2 alphas preceding the first digits

[Greg_Norman]

Thanks,

I've tried running it but am getting an Invalid Outside Procedure message.

Any Idea why this could be?

Regards

[DonkeyOte]

Sounds as though there is a problem with the add-in, run the uninstall.exe and then try reinstalling, I am presuming without the add-in you're not experiencing any issues ?

If problems persist post back and we will convert the above into a UDF.

[Greg_Norman]

Hey,

I had the formula added in but its not returning anything.

Are there any other suggestions?

Thanks Again

[DonkeyOte]

The formula provided worked without incident based on your sample strings, please post a sample file with the strings that are failing to produce a result so that we can replicate.

[Greg_Norman]

Heres a few examples, if you can get it working here I should be able to work the rest.

Thanks

[DonkeyOte]

OK there were some discrepancies as to whether or not the space between first and second part of the Post Code exists so this slight revision worked for all but one string (row 20 given comma amidst postcode):

D2: =REGEX.MID(A2&B2&C2,"([a-z]{1,2}[0-9]+\s{0,1}[0-9][a-z]{2})",,FALSE)
copied down

[Greg_Norman]

Are you putting this code in VB or straight into the cell D1?

I have installed morefunc but my VB skills arent too good.

Thanks

[DonkeyOte]

This is a function (utilising the add-in you've installed) and as such is entered directly into cell D2.
(essentially REGEX.MID and all other morefunc Functions are used as you would use any other function, eg VLOOKUP, MATCH etc...)

[Greg_Norman]

Not to worry, it works.

Thanks a lot!!