Left Function Question Help!

[James8309]

Hi Everyone

I have this formulae in my cell;

=if(left(a1,1)="A",1,if(left(a1,1)="B",2,if(left(a1,1)="C",
3,...................

and so on.

Question is.. I have to put in 'left(a1,1) each time I give
conditions. Is there other method of making this formulae simpler? so
I don't have to type left(a1,1) =

Thank you for your help in advance.


Regards


James

 

[Pete_UK]

You seem to be allocating a sequential number depending on the first
letter of A1. This formula will do that for you:

=CODE(UPPER(A1))-64

This will allocate 1 for "A" and "a", 2 for "B" and "b", 3 for "C" and
"c" etc up to 26 for "Z" and "z".

Hope this helps.

Pete

 

[James8309]

You seem to be allocating a sequential number depending on the first
letter of A1. This formula will do that for you:

=CODE(UPPER(A1))-64

This will allocate 1 for "A" and "a", 2 for "B" and "b", 3 for "C" and
"c" etc up to 26 for "Z" and "z".

Hope this helps.

Pete






- Show quoted text -

Thanks for your help.

What happens if it is not allocating sequential number? e.g. A = 1 B =
3 C = 2 D = 9 ...etc

 

[Pete_UK]

Well, you could set up a little table somewhere like this:

A 1
B 3
C 2
D 9
etc.

Suppose this is in X1:Y26. Then you can use this formula:

=VLOOKUP(LEFT(UPPER(A1),1),$X$1:$Y$26,2,0)

Again, this treats upper and lower case letters the same. If the
values change, you only need to change the table - no need to adjust
the formula.

Hope this helps.

Pete