Excel: Extracting a UK postcode from a string

Have some addresses in comma delimited form within a cell, but the format is all over the place so if I do text to column, the postcode can end up in as many as four different columns. The postcode isn't always at the end either - it is sometimes followed by UK. If I use the SEARCH function, I can pick out a postcode in a particular format and return the numeric value of where it starts - and could then use that to extract I guess, but I don't know how to cater for the fact there are three variations in length on a UK postcode (and 6 formats). In essence, what I want to do is search in a cell for ",?? ???," and ",??? ???," and ",???? ???," and whichever format of the three is found, extract postcode minus commas to the adjoining cell. There is always a postcode to be found in the cell being searched. So you could have permutations like this (and more) in the data. House number,street,town,LLN NLL, House number,street,district,city,LNN NLL,UK Flat number,building number,street,district,city,county,LLNN NLL,UK Building name,building purpose,street,city,county,LN NLL, Any help appreciated! Thanks.

This question generated 14 answers. To proceed to the answers, click here.

This thread is current as of June 03, 2017.

For more resources for Microsoft Excel