Forum Discussion
excel_learner
Jul 08, 2022Brass 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 |
- excel_learnerBrass Contributor
Thank You, this works
excel_learner , you are welcome
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.