SOLVED

# Using Left Function with If statement

Brass Contributor

# Using Left Function with If statement

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  5UZ SW SW3  5UZ SW LU1  1TU LU W8   4RN W8 UB8  2DW UB W11  4RS W1 N10  3TE N1 HA0  4JU HA NW10 2QD NW
5 Replies

# Re: Using Left Function with If statement

That could be

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

# Re: Using Left Function with If statement

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

# Re: Using Left Function with If statement

Or

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

# Re: Using Left Function with If statement

Thank You, this works

# Re: Using Left Function with If statement

@excel_learner , you are welcome