=SUBSTITUTE - UK Post Codes

[dave3009]

Hi Folks

I have seen the answer to this but my powers of searching have failed me. How do I turn a UK postcode NW1 4DA for example into just NW, bearing in mind there may be an additional number/letter in the mix.

I'm sure it's =SUBSTITUTE but I don't really know how that function works.

Thanks in advance for any help


Dave

 

[atmospheric]

=left(A1,2)

Substitute A1 for cell in question

 

[dave3009]

Thank you, appreciate it. I'll remember that one for future

 

[barry houdini]

If you want to extract just the letters, e.g. NW from NW1 4DA but only W from W1 1AA try this formula

=LEFT(A1,1+ISERR(MID(A1,2,1)+0))

 

[dave3009]

Thank you Barry

I was just about to ask that very question.

KR


Dave

 

[dave3009]

Find/Replace Wildcards

Hi All

Is it possible to use a wildcard in find/replace. In my post I basically wanted to get rid of all the specific area data just to bring me down to a larger area thus anonymising peoples names, I would also like all my post codes to be in double letter. The best way I reckon I could do this is by adding a Z to the end of any single letter areas as Z isn't used in the UK postal code system.

Thanks for everyone's help


Dave