SOLVED

Using Left Function with If statement

Brass Contributor

I am using the Left function to get first two letters of a Postal Code, however in some of the postal codes the first two characters contains a number. How can I ignore the number in the postal codes which have a number in the first two characters.

For example below W8, W1, N1. The numbers would need to be removed leaving just W, W and N.

Is there a formula using the LEFT function with an IF statement to do this ?

SW3  5UZSW
SW3  5UZSW
LU1  1TULU
W8   4RNW8
UB8  2DWUB
W11  4RSW1
N10  3TEN1
HA0  4JUHA
NW10 2QDNW
5 Replies

@excel_learner 

That could be

=IF( ISNUMBER( --RIGHT( LEFT(A1,2) ) ), LEFT(A1), LEFT(A1, 2) )

@excel_learner 

With a postal code in A1:

=LEFT(A1,MATCH(TRUE,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=57,0)-1)

If you don't have Microsoft 365 or Office 2021, you'll probably have to confirm the formula with Ctrl+Shift+Enter.

Fill down.

best response confirmed by Grahmfs13 (Microsoft)
Solution

@excel_learner 

Or

=LEFT(A1, 2 - ISNUMBER( --RIGHT( LEFT(A1,2) ) ))

@Sergei Baklan 

Thank You, this works

@excel_learner , you are welcome

1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@excel_learner 

Or

=LEFT(A1, 2 - ISNUMBER( --RIGHT( LEFT(A1,2) ) ))

View solution in original post