Postcode Prefix formula

[waxb18]

I have a series of post codes and need to extract the first letter(s) and first number(s),

EG:

AL1 3NF = AL1

S1 4NF = S1

GU12 9NF = GU12

Ive tried =left([cell],4), but this then includes the space in the AL1 code and in case of the S1 postal code include the 4,
so basically im asking if someone would help me by providing a formula???

 

[waxb18]

check the format of the cell... should be in text format or general,

Other thing what version of excel are you using, try removing the iferror

 

[projectile]

Using Excel 2003

The cell is general , also tried text

Removing IFERROR doesn't work ( the formula you typed contains an error)

 

[waxb18]

it works fine for me....i dont know where your going wrong.
Can you post a sample of your data?

 

[projectile]

here is sample of the post codes from ODBC extract

M17 1SA
TW4 6NH
SS14 3OI
B1 3AP

 

[waxb18]

it works fine on my machine, im unsure of where the error could be,

Do one thing for me, type a postcode in manually then tell me if my formula works or not?

 

[projectile]

hi

I tried that and same thing

I have uploaded an example spreadsheet with the postcodes and your formula, can you download the sheet from google docs and let me know if you are able to get it working.

https://docs.google.com/leaf?id=0B8...MDEyZS00ZjlkLThkODEtMjM1ZjhlYzA0ZjFm&hl=en_US

 

[West Man]

Try: =LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"0123456789"))-1)

 

[projectile]

YES, that's exactly what I was trying to do!

Thanks

 

[West Man]

You are welcome. Glad to be of help.

 

[projectile]

You are welcome. Glad to be of help.

Can you advise what the MIN function does in this formula?