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

 

[paulsolar]

Hi Mark

yours work brilliantly as well, spoilt for choice now

Cheers

Paul

 

[Aladin Akyurek]

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

You are welcome. It's a fast method.

 

[markmzz]

Hi Mark

yours work brilliantly as well, spoilt for choice now

Cheers

Paul

Hi Paul!

I'm happy that your problem was solved. This is the most important thing.

And thank you for the feedback.

By the way, here is another option (not so fast, but that works):

=LEFT(C4,AGGREGATE(15,6,FIND(ROW($1:$10)-1,C4),1)-1)

Markmzz