Extract Postcode sector

[swaink]

Hi All

I wonder if you can help me solve my headache

I have been asked to extract the postcode sector from a cell that contains the whole postal address.

In the spreadsheet the address is in Column F seperated by a comma,

In a spare column I have placed a formula which succesfully retrieves the whole of the postcode but I don't want to see the last two characters in the result.

As an example I have used
=RIGHT(F2,LEN(F2)-SEARCH("@",SUBSTITUTE(F2," ","@",LEN(F2)-LEN(SUBSTITUTE(F2," ",""))))) this produces the result of WF61TF

What I now am trying to acheive is that this would reflect WF61 only

Would anyone know how I may tweak this formula to acheive this as I have run out of ideas

All help appreciated

Many thank

Kevin

 

[anandvarma]

Try this

=replace(b1,1,min(find({0,1,2,3,4,5,6,7,8,9},b1&"0123456789"))-1,"")

this extract numbers from any text

 

[RichardMGreen]

Try this:-
=left(RIGHT(F2,LEN(F2)-SEARCH("@",SUBSTITUTE(F2," ","@",LEN(F2)-LEN(SUBSTITUTE(F2," ",""))))),len(RIGHT(F2,LEN(F2)-SEARCH("@",SUBSTITUTE(F2," ","@",LEN(F2)-LEN(SUBSTITUTE(F2," ",""))))))-2)

I think I've got the right number of brackets in place, but it's probably worth a check.

 

[swaink]

Hi Both

Really appreciate the help, Richard i've gone with yours and tested it.

The formula does now return what I would expect to see

Many thanks for your help

Kevin

 

[RichardMGreen]

Glad it's working for you.

 

[Peter_SSs]

Does one of these also do what you want?

=LEFT(RIGHT(F2,6),4)

=TRIM(RIGHT(SUBSTITUTE(LEFT(F2,LEN(F2)-2)," ",REPT(" ",100)),100))

If not, any chance of a few rows of dummy (but realistic) data for column F to show any variations that might occur to see if there isn't something a bit simpler than the previous suggestion? The expected results would also be useful.