Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Need formula to increase Alpha character to next letter

I am looking for a formula that will increase by one letter the alpha
designation in a column. ex. A2 would be the letter 'A', B2 which would show
the next letter of the alphabet should be 'B'. I know how to increase a
number, ex. =(A2+1), how do I do the alphabet?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Need formula to increase Alpha character to next letter

Sure, I can help you with that! You can use the
Code:
CHAR
and
Code:
CODE
functions in Excel to increase the alpha character to the next letter. Here's how:
  1. In the cell where you want to display the next letter, enter the following formula:
    Code:
    =CHAR(CODE(A2)+1)
  2. In this formula,
    Code:
    A2
    is the cell that contains the current letter. The
    Code:
    CODE
    function returns the ASCII code for the letter in cell A2, and the
    Code:
    +1
    increases the code by one.
  3. The
    Code:
    CHAR
    function then converts the new ASCII code back into a letter.
  4. Copy the formula down the column to apply it to all the cells.

That's it! Now, each cell in the column will display the next letter in the alphabet. Let me know if you have any questions or if there's anything else I can help you with.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Need formula to increase Alpha character to next letter

Why do you need to repeat post?

Posted the response below in your earlier thread:
If A2 contains: A,
then this in say B2: =CHAR(CODE(A2)+1)
will return the next letter: B

voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"pwinters" wrote:
I am looking for a formula that will increase by one letter the alpha
designation in a column. ex. A2 would be the letter 'A', B2 which would show
the next letter of the alphabet should be 'B'. I know how to increase a
number, ex. =(A2+1), how do I do the alphabet?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default Need formula to increase Alpha character to next letter

=CHAR(CODE(A2)+1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"pwinters" wrote in message
...
I am looking for a formula that will increase by one letter the alpha
designation in a column. ex. A2 would be the letter 'A', B2 which would
show
the next letter of the alphabet should be 'B'. I know how to increase a
number, ex. =(A2+1), how do I do the alphabet?


  #5   Report Post  
Junior Member
 
Posts: 1
Default

This works fine for up to Z then if want AA, AB, AC... what should be done.





Quote:
Originally Posted by Max View Post
Why do you need to repeat post?

Posted the response below in your earlier thread:
If A2 contains: A,
then this in say B2: =CHAR(CODE(A2)+1)
will return the next letter: B

voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"pwinters" wrote:
I am looking for a formula that will increase by one letter the alpha
designation in a column. ex. A2 would be the letter 'A', B2 which would show
the next letter of the alphabet should be 'B'. I know how to increase a
number, ex. =(A2+1), how do I do the alphabet?


  #6   Report Post  
Junior Member
 
Posts: 3
Default

I found this on another site:

"if you put A1 inside row(), then you can put the formula in
starting in any row and drag fill it down:

=CHAR((ROW(A1)-1)/26+65)&CHAR(MOD(ROW(A1)-1,26)+65)

(my own version of the formula)

as an example.


Regards,
Tom Ogilvy"

For me, this worked for AA, then for AB, I had to change the "A1"s to "A2"s, then I copied and Pasted-Special for the rest of the column, and it worked.

From: http://www.pcreview.co.uk/threads/ho...to-zz.1010611/

Quote:
Originally Posted by kumar35885 View Post
This works fine for up to Z then if want AA, AB, AC... what should be done.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
if alpha character samuel Excel Discussion (Misc queries) 4 December 17th 08 04:22 AM
Prevent alpha character in numeric cell Brazil Excel Discussion (Misc queries) 2 July 21st 08 09:28 PM
add alpha letter to column of numbers -John W. New Users to Excel 6 January 9th 07 04:21 AM
Parsing a alpha character out of a cell Lram Excel Worksheet Functions 7 October 17th 05 10:56 PM
Numerical grade to Alpha character capecrusader Excel Discussion (Misc queries) 6 August 20th 05 02:02 PM


All times are GMT +1. The time now is 06:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"