Formula to Increment by Letter not Number


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
 
Ad

Advertisements

M

Max

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
 
J

JoeU2004

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...

=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 -----
 
S

Shane Devenshire

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?
 
R

Rob

Thank You Very Very Much!! That was Exactly what I was looking for and
Hoping to learn.

Cheers!
 
R

Rob

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

Outstanding!
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top