Help with a formula

[paulsolar]

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

 

[Aladin Akyurek]

Care to give an example value C4 might house and the ultimate result that must obtain from it?

 

[paulsolar]

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

 

[hotabae]

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))

 

[Aladin Akyurek]

When

C4 = B12 3QR

what is the end result we want?

Also when

C4 = NP12 3TT

what is the end result we want?

 

[paulsolar]

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

 

[paulsolar]

Hi Hotabae

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

cheers

paul

 

[Aladin Akyurek]

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))

 

[markmzz]

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

 

[paulsolar]

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