| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Increment by Letter not Number
I was wondering if there is a way to make a formula increment to the next
letter character instead of the next number. For instance... =IF(B3="","",A2+1) will give me 1, 2, 3, 4 etc... but what if I want to go by letters or even add letters to the end? =IF(B3="","",IF(B3<B2,A2+1,A2&"a")) where A2 is either 0 or 1 =IF(B3="","",IF(B3<B2,A2+1)) where A2 is either A or AA or 1A or A1 Well, Thanks In Advance, Rob |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to Increment by Letter not Number
This, in any startcell, copied down by 26 rows:
=CHAR(ROWS($1:1)+64) will generate the series (cap alphas): A, B, ... Z Hence, for eg, you could use it like this: =IF(B3="","",CHAR(ROWS($1:1)+64)) copied down -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Rob" wrote: I was wondering if there is a way to make a formula increment to the next letter character instead of the next number. For instance... =IF(B3="","",A2+1) will give me 1, 2, 3, 4 etc... but what if I want to go by letters or even add letters to the end? =IF(B3="","",IF(B3<B2,A2+1,A2&"a")) where A2 is either 0 or 1 =IF(B3="","",IF(B3<B2,A2+1)) where A2 is either A or AA or 1A or A1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to Increment by Letter not Number
"Rob" wrote:
I was wondering if there is a way to make a formula increment to the next letter character instead of the next number. For instance... =IF(B3="","",A2+1) will give me 1, 2, 3, 4 etc... but what if I want to go by letters =if(B3="", "", char(code(A2)+1)) or even add letters to the end? =IF(B3="","",IF(B3<B2,A2+1,A2&"a")) where A2 is either 0 or 1 =IF(B3="","",IF(B3<B2,A2+1)) where A2 is either A or AA or 1A or A1 Sorry, but these do not make sense to me. In the first formula, if A2 is 0 or 1, what is wrong with the A2+1 or A2&"a"? The first will result in 1 or 2; the second will result in "0a" or "1a". If that is not the result you want, exactly what result do you want? In the second formula, A2+1 makes no sense if A2 is "A", "AA", "1A" or "A1". And it makes no more sense to me in the context of your original question. Again, exactly what result do you want? PS: In the second formula, it is poor form to omit the "value_if_false" part. Your formula will result in FALSE if B3 is not "" and B3=B2. If you need help, it would be prudent to explain what result you want in that case, too. ----- original message ----- "Rob" wrote in message ... I was wondering if there is a way to make a formula increment to the next letter character instead of the next number. For instance... =IF(B3="","",A2+1) will give me 1, 2, 3, 4 etc... but what if I want to go by letters or even add letters to the end? =IF(B3="","",IF(B3<B2,A2+1,A2&"a")) where A2 is either 0 or 1 =IF(B3="","",IF(B3<B2,A2+1)) where A2 is either A or AA or 1A or A1 Well, Thanks In Advance, Rob |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to Increment by Letter not Number
Hi,
It's would help if you show us what you want for the results it might help. I sort of assume you want to copy to the right but Max thinks its down, so could you clarify. For example you might show us the results you want as: A B C or A B C Futher its not clear to me what you are trying to do with A2&"a" And this line - "=IF(B3="","",IF(B3<B2,A2+1)) where A2 is either A or AA or 1A or A1" is unclear? If A2 contains AA then A2+1 make no sense to me? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Rob" wrote: I was wondering if there is a way to make a formula increment to the next letter character instead of the next number. For instance... =IF(B3="","",A2+1) will give me 1, 2, 3, 4 etc... but what if I want to go by letters or even add letters to the end? =IF(B3="","",IF(B3<B2,A2+1,A2&"a")) where A2 is either 0 or 1 =IF(B3="","",IF(B3<B2,A2+1)) where A2 is either A or AA or 1A or A1 Well, Thanks In Advance, Rob |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to Increment by Letter not Number
Thank You Very Very Much!! That was Exactly what I was looking for and
Hoping to learn. Cheers! "Max" wrote: This, in any startcell, copied down by 26 rows: =CHAR(ROWS($1:1)+64) will generate the series (cap alphas): A, B, ... Z Hence, for eg, you could use it like this: =IF(B3="","",CHAR(ROWS($1:1)+64)) copied down -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Rob" wrote: I was wondering if there is a way to make a formula increment to the next letter character instead of the next number. For instance... =IF(B3="","",A2+1) will give me 1, 2, 3, 4 etc... but what if I want to go by letters or even add letters to the end? =IF(B3="","",IF(B3<B2,A2+1,A2&"a")) where A2 is either 0 or 1 =IF(B3="","",IF(B3<B2,A2+1)) where A2 is either A or AA or 1A or A1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to Increment by Letter not Number
Thanks Tons! Your info confirmed the previous and I truly appreciate your time.
Outstanding! "JoeU2004" wrote: "Rob" wrote: I was wondering if there is a way to make a formula increment to the next letter character instead of the next number. For instance... =IF(B3="","",A2+1) will give me 1, 2, 3, 4 etc... but what if I want to go by letters =if(B3="", "", char(code(A2)+1)) or even add letters to the end? =IF(B3="","",IF(B3<B2,A2+1,A2&"a")) where A2 is either 0 or 1 =IF(B3="","",IF(B3<B2,A2+1)) where A2 is either A or AA or 1A or A1 Sorry, but these do not make sense to me. In the first formula, if A2 is 0 or 1, what is wrong with the A2+1 or A2&"a"? The first will result in 1 or 2; the second will result in "0a" or "1a". If that is not the result you want, exactly what result do you want? In the second formula, A2+1 makes no sense if A2 is "A", "AA", "1A" or "A1". And it makes no more sense to me in the context of your original question. Again, exactly what result do you want? PS: In the second formula, it is poor form to omit the "value_if_false" part. Your formula will result in FALSE if B3 is not "" and B3=B2. If you need help, it would be prudent to explain what result you want in that case, too. ----- original message ----- "Rob" wrote in message ... I was wondering if there is a way to make a formula increment to the next letter character instead of the next number. For instance... =IF(B3="","",A2+1) will give me 1, 2, 3, 4 etc... but what if I want to go by letters or even add letters to the end? =IF(B3="","",IF(B3<B2,A2+1,A2&"a")) where A2 is either 0 or 1 =IF(B3="","",IF(B3<B2,A2+1)) where A2 is either A or AA or 1A or A1 Well, Thanks In Advance, Rob |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to Increment by Letter not Number
Welcome, glad to hear
-- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Rob" wrote in message ... Thank You Very Very Much!! That was Exactly what I was looking for and Hoping to learn. Cheers! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula copy paste down in a sheet but change row letter increment | Excel Discussion (Misc queries) | |||
| formula converting number to column letter 26 | Excel Worksheet Functions | |||
| Help to write a formula using a letter value and a number value | Excel Worksheet Functions | |||
| change headers from letter to number/number to letter | Excel Worksheet Functions | |||
| column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) | |||
Linear Mode
