=LEFT function

**PC_Excel** · 03-12-2020, 09:37 AM

Hi all,

First post so please go easy on me...

I'm looking at using the =LEFT() fucntion to take the first 1 or 2 LETTERS (not numbers) of the a postcode, for example if a postcode is SW8 2AA, the formula would produce 'SW' or if the post code is S8 2AA the formula would produce 'S'.

The alternative is to use the =LEFT(CELL,1 or 2), but this requires manual intervention when a psotcode has 1 or 2 letters.

I've tried searching for this solution on Google but it doesn't bring back anything useful.

Thanks,
PC

**KOKOSEK** · 03-12-2020, 09:53 AM

Something like this seems to work:

	A	B
1	SW8 2AA	SW
2	S8 2AA	S

	A	B
1	SW8 2AA	=TRIM(LEFT(TEXTJOIN("",1,IF(ISNUMBER(MID(A1,SEQUENCE(LEN(A1)),1)*1),REPT(" ",100),MID(A1,SEQUENCE(LEN(A1)),1))),20))
2	S8 2AA	=TRIM(LEFT(TEXTJOIN("",1,IF(ISNUMBER(MID(A2,SEQUENCE(LEN(A2)),1)*1),REPT(" ",100),MID(A2,SEQUENCE(LEN(A2)),1))),20))

EDIT: when I see jeffrey's formula below, I see that I've overdosed.

**jeffreybrown** · 03-12-2020, 09:53 AM

How about in B2 copied down assuming your data starts in A2

=IF(LEN(LEFT(A2,FIND(" ",A2)-1))=3,LEFT(A2,2),LEFT(A2))

Edit: Maybe that won't work for you as you did say not numbers

**AlKey** · 03-12-2020, 10:20 AM

Or try this

Enter formula in Cell B1 and copy down

Formula:

Please Login or Register  to view this content.

v	A	B
1	SW8 2AA	SW
2	S8 2AA	S

**Naren_Kumar** · 03-12-2020, 10:22 AM

Place you value in cell A1 and use below formula: (See attachment for details)

=IF(AND(--ISNUMBER(VALUE(MID(A1,1,1)))=0,--ISNUMBER(VALUE(MID(A1,2,1)))=0),LEFT(A1,2),IF(AND(--ISNUMBER(VALUE(MID(A1,1,1)))=0,--ISNUMBER(VALUE(MID(A1,2,1)))=1),LEFT(A1,1),""))

Hope it helped you?