Help with a formula

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
670
Office Version
  1. 365
Hi All

I'm looking to combine 2 formula rather than having to use 2 cells to obtain the answer.

In cell D4 I have =LEFT(C4,2)
In cell E4 I have =IF(ISNUMBER(RIGHT(D4,1)),D4,LEFT(D4,1))

This is being used convert a full post code to just the opening letters, for example a post code be B12 3TT or for a different area it could be BA12 3TT, I just want either B or BA

I've tried all sorts to get it to work in one cell and abviously doing something wrong.

there is probably a lot more elegant way of doing this but I'll be blowed if I can find it

Any help would be greatly appreciated


Cheers

Paul
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi Aladin

No problem

C4 could contain something like B12 3QR or NP12 3TT "left(C4,2)" will contain either one letter and a number or two letter, I just want either the one letter or both of the letters.

the formula i enterd will do this but i have to use two cells to achieve the result.

cheers

Paul
 
Upvote 0
I'm looking to combine 2 formula rather than having to use 2 cells to obtain the answer.

In cell D4 I have =LEFT(C4,2)
In cell E4 I have =IF(ISNUMBER(RIGHT(D4,1)),D4,LEFT(D4,1))
l


Voila,

I put them together for you!

To go in Cell D4 I guess
=IF(ISNUMBER(RIGHT(LEFT(C4,2),1)),LEFT(C4,2),LEFT(LEFT(C4,2),1))
 
Last edited:
Upvote 0
Hi Aladin

When


C4 = B12 3QR I want B

C4 = NP12 3TT I want NP


<colgroup><col></colgroup><tbody></tbody>
B65 8BD = B
LA14 3JZ =LA
CV6 1BA =CV
S26 4UH=S
CO9 4JJ =CO
NR136RE =NR
S64 8QY = S
 
Upvote 0
Hi Hotabae

I tried you method and it only returns one letter not two

cheers

paul
 
Upvote 0
Thanks for the sample and the desired results...

In D4 enter and copy down:

=LEFT(C4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C4&"0123456789")-1))
 
Upvote 0
Hi Aladin

When


C4 = B12 3QR I want B

C4 = NP12 3TT I want NP

B65 8BD = B
LA14 3JZ =LA
CV6 1BA =CV
S26 4UH=S
CO9 4JJ =CO
NR136RE =NR
S64 8QY = S

Hi!

Try this in D4:

=LEFT(C4,MATCH(1,INDEX(--ISNUMBER(-MID(C4,ROW($1:$9),1)),),0)-1)

Markmzz
 
Last edited:
Upvote 0
Hi Aladin

perfect thanks, altough i have to admit left to my own devices i would never have come up with your method of doing it

brillaint thanks

cheers

Paul
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top