# Further Trimming postcode issue

#### RICK150

##### New Member
Hello There, just wondering if you guys mind helping me out once again with yet another small postcode issue.

I'll try to explain:-

In column A i have a list of UK postcodes. The problem is i just want the letters in the first half of the postcode. and the postcodes vary. So

LE67 2QN should return LE
B62 1AA should return B
E1W 1AA should return E

I've managed to get the previous query working about splitting it at the space but now would like to spilt one further too and am struggling.

If anyone could help me at all i'd really appreciate it.

Thanks and if you need any clarity let me know.

Ricky

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
If your postcode is in A1 use this formula to return either first letter or first 2 letters

=LEFT(A1,1+ISERR(MID(A1,2,1)+0))

I'm afraid to say this hasn't worked.

It works great for postcodes with two letters EG :-

'LE65 1AA' does return 'LE'
'DE39 1AA' does return 'DE'
'W1 1AA' RETURNS 'W1' not 'W'

you can't work it off the space in the postcode. I need something that detects when number start in string then i can left the result of that to limit it just to numbers.

Unless that is anyone else has any other ideas that will work i'm perfectly willing to listen to other suggestions with thanks.

Many thanks in advance for any help anybody can give me.

THANK YOU

RICKY

Hi Ricky

I just tried Barry's formula and it works for me.

'W1 1AA' returns 'W', as you want.

Check the last part of the formula. Maybe by accident you typed +O (letter O) instead of +0 (number zero)?

Hope this helps
PGC

AHA how stupid do I feel now My apologies for this its working fine, and would have done first time if I could type!!?!?

Thank you all for your help and patiaence with me.

THANK YOU THANK YOU THANK YOU

Ricky (Far from worthy)

Replies
8
Views
371
Replies
0
Views
278
Replies
1
Views
975
Replies
13
Views
997
Replies
8
Views
6K

1,203,140
Messages
6,053,727
Members
444,681
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back