Formula to Increment by Letter not Number

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

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
 
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 said:
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...
Click to expand...

=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
Click to expand...

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 -----
 
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?
 
Thank You Very Very Much!! That was Exactly what I was looking for and
Hoping to learn.

Cheers!
 
Thanks Tons! Your info confirmed the previous and I truly appreciate your time.

Outstanding!
 
Back
Top