Extracting a UK postcode from a string

[YorkshireMidge]

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.

 

[inactiveuserps07]

Yorkshire, if you're up for trying one more formula variation, see if this catches the exceptions:

=TRIM(MID(TRIM(A1)&CHAR(44),FIND(",",TRIM(A1)&CHAR(44),(FIND(" ",TRIM(A1)&CHAR(44),LEN(TRIM(A1)&CHAR(44))-8)-6))+1,(FIND(",",TRIM(A1)&CHAR(44),(FIND(" ",TRIM(A1)&CHAR(44),LEN(TRIM(A1)&CHAR(44))-8)))-FIND(",",TRIM(A1)&CHAR(44),(FIND(" ",TRIM(A1)&CHAR(44),LEN(TRIM(A1)&CHAR(44))-8)-6)))-1))

 

[inactiveuserps07]

You said the exceptions were where a comma was missing after the postcode. The above formula will catch everything that it already did, AS WELL as instances where the entry ENDS with the postcode and no comma.

If, however, you're saying that there are entries where the postcode was followed by "UK" with no comma between the two, this formula won't work on those.

It also won't work if there are instances where "UK" is followed by a comma, for some reason (though I can't imagine why it would be).

If you still wind up with exceptions, you could use conditional formatting to highlight the "misses."

Set up conditional formatting on the entire column that contains my output formula:

Home Tab > Conditional Formatting > New Rule > "Use a formula to determine which cells to format"

Then enter this rule: =AND(ISERROR(FIND(" ",B1)),B1<>"")

(If your output column isn't B, you'll want to change that conditional formula to whatever column your output is in.)

And remember ... if you want to avoid any exceptions, you should be able to just select the entire original list column and use Find&Replace from the Home Tab > Editing section to Find: [comma space] and Replace with: [comma] ... where actual commas and spaces are entered, not words.

 

[SamehM]

if the data in cell A1:A4 .. I used this formula

=RIGHT(IF((RIGHT(SUBSTITUTE(A1,",UK",""),1)=","),SUBSTITUTE((RIGHT(SUBSTITUTE(A1,",UK",""),1)),",",LEFT(SUBSTITUTE(A1,",UK",""),LEN(SUBSTITUTE(A1,",UK",""))-1)),SUBSTITUTE(A1,",UK","")),10-SEARCH(",",RIGHT(IF((RIGHT(SUBSTITUTE(A1,",UK",""),1)=","),SUBSTITUTE((RIGHT(SUBSTITUTE(A1,",UK",""),1)),",",LEFT(SUBSTITUTE(A1,",UK",""),LEN(SUBSTITUTE(A1,",UK",""))-1)),SUBSTITUTE(A1,",UK","")),10)))

 

[Rick Rothstein]

I think this formula should work for any of your text values...

[table="width: 500"]
[tr]
	[td]=TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1,"UK",""),","," "))," ",REPT(" ",200)),400),400))[/td]
[/tr]
[/table]

 

[SamehM]

I think this formula should work for any of your text values...

[TABLE="width: 500"]
<tbody>[TR]
[TD]=TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1,"UK",""),","," "))," ",REPT(" ",200)),400),400))[/TD]
[/TR]
</tbody>[/TABLE]

Mr. Rick your formula is absolutely wonderful .. i like it so much .. just how can you apply it if the UK is writen in different cases like this Uk or uk or uK .. i had a hard time trying to find a solution for this

 

[Rick Rothstein]

Mr. Rick your formula is absolutely wonderful .. i like it so much .. just how can you apply it if the UK is writen in different cases like this Uk or uk or uK .. i had a hard time trying to find a solution for this

Thanks for those nice words about my formula, I really appreciate them. As for your question, this should do it...

=TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"UK",""),","," "))," ",REPT(" ",200)),400),400))

 

[YorkshireMidge]

Yorkshire, if you're up for trying one more formula variation, see if this catches the exceptions:

=TRIM(MID(TRIM(A1)&CHAR(44),FIND(",",TRIM(A1)&CHAR(44),(FIND(" ",TRIM(A1)&CHAR(44),LEN(TRIM(A1)&CHAR(44))-8)-6))+1,(FIND(",",TRIM(A1)&CHAR(44),(FIND(" ",TRIM(A1)&CHAR(44),LEN(TRIM(A1)&CHAR(44))-8)))-FIND(",",TRIM(A1)&CHAR(44),(FIND(" ",TRIM(A1)&CHAR(44),LEN(TRIM(A1)&CHAR(44))-8)-6)))-1))

Works great!

 

[YorkshireMidge]

Thanks for those nice words about my formula, I really appreciate them. As for your question, this should do it...

=TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"UK",""),","," "))," ",REPT(" ",200)),400),400))

This also works perfectly Thanks!