Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Left Function Question Help!
Hi Everyone
I have this formulae in my cell; =if(left(a1,1)="A",1,if(left(a1,1)="B",2,if(left(a 1,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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Left Function Question Help!
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 On Aug 22, 1:04*am, James8309 wrote: Hi Everyone I have this formulae in my cell; =if(left(a1,1)="A",1,if(left(a1,1)="B",2,if(left(a 1,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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Left Function Question Help!
On Aug 22, 10:17*am, Pete_UK wrote:
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 On Aug 22, 1:04*am, James8309 wrote: Hi Everyone I have this formulae in my cell; =if(left(a1,1)="A",1,if(left(a1,1)="B",2,if(left(a 1,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- Hide quoted text - - 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Left Function Question Help!
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 On Aug 22, 1:19*am, James8309 wrote: On Aug 22, 10:17*am, Pete_UK wrote: 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 On Aug 22, 1:04*am, James8309 wrote: Hi Everyone I have this formulae in my cell; =if(left(a1,1)="A",1,if(left(a1,1)="B",2,if(left(a 1,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- Hide quoted text - - 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- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
Left vs Left$ function | Excel Discussion (Misc queries) | |||
Question for about using IF formula LEFT function at the same time | Excel Worksheet Functions | |||
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions | |||
Question about using the right or left formula | Excel Discussion (Misc queries) |