# Thread: Does Excel Know the Alphabet? (Excel 2K)

1. ## Does Excel Know the Alphabet? (Excel 2K)

I know that the vbKey Codes can be set to Alphabetic values, but can I get Excel to "count" alphabetically?

2. ## Re: Does Excel Know the Alphabet? (Excel 2K)

Hi Catharine,

I'm not sure I understand your question, but if you want a series of items to be numbered a, b, c, ... instead of 1, 2, 3, ..., you can insert an extra column to the right of the 1, 2, 3, ... and fill it with formulas like =CHAR(96+A1) where A1 is a cell containing a number (if you prefer upper case instead of lower case, use 64 instead of 96.) You can then hide the numeric column.

If you meant something else, please explain.

3. ## Re: Does Excel Know the Alphabet? (Excel 2K)

nope, that will work ...

4. ## Re: Does Excel Know the Alphabet? (Excel 2K)

If you put the formula below in any cell in row 1 and copy it down, it will "number" the cells a through z and then start over at a.

<pre>=CHAR(MOD(ROW()-1,26)+97)
</pre>

5. ## Re: Does Excel Know the Alphabet? (Excel 2K)

Legare

We import data from another application. When the data comes over each row is numbered, but there was a request yesterday to change the numbers into letters a-z . The first row of data might start in row 6, but the numbering will need to start at 1. My suggestion was to use custom list and change it manually. Is there a formula for this. VB would not be an option here.

Thanks

6. ## Re: Does Excel Know the Alphabet? (Excel 2K)

If you only have 26 you can use
=CHAR(96+A1) will get you "a-z"
=CHAR(64+A1) will get you "A-Z"

If you want to to get those 26 + the 676 2 digit (702 total):
=IF(A1<=26,"",CHAR(INT((A1-1)/26)+96))&CHAR(MOD(A1-1,26)+97) for "a-zz"
=IF(A1<=26,"",CHAR(INT((A1-1)/26)+64))&CHAR(MOD(A1-1,26)+65) for "A-ZZ"

This assumes you start in A1, copy as appropriate.

Steve

7. ## Re: Does Excel Know the Alphabet? (Excel 2K)

Hi Steve

Unfortunately, the data doesn't start in row 1. There is usually other blurb on rows 1 -7 and this can vary each time the data is imported. Sort of a mystery spreadsheet. But I have kept copies of all the formulas given in this thread and put them on a spreadsheet for future reference.

8. ## Re: Does Excel Know the Alphabet? (Excel 2K)

This was only an example. I used the "generic formula" with the item "number" in cell A1.

If your "numbers" start in A7 just replace the A1s with A7s. It does not depend on the row number of the cell, it depends on the value in the cell that you are referencing.

Steve

9. ## Re: Does Excel Know the Alphabet? (Excel 2K)

<img src=/S/blush.gif border=0 alt=blush width=15 height=15> Should have known better

Passed examples over to those that need it and they are really pleased

THANKS

10. ## Re: Does Excel Know the Alphabet? (Excel 2K)

I see that you have already gotten some other solutions. For completeness, the following modification to my formula should work if it starts in row 6:

<pre>=CHAR(MOD(ROW()-ROW(\$A\$6),26)+97)
</pre>

To change it to work starting in any other row, just change the \$A\$6 to the row that it starts in.

11. ## Re: Does Excel Know the Alphabet? (Excel 2K)

I asked my question, and forgot about it. But it seems to have sparked a lot of discussion.
For what it is worth - here is my final solution:

ReDim BinLabelArray(IntBins, 1)
intBinCounter = 1
For intBinCounter = 1 To IntBins
BinLabelArray(intBinCounter, 1) = Chr(64 + intBinCounter)
Next intBinCounter

I needed to create an array of letters, and I won't know how long the array is... thus my question about counting.

12. ## Re: Does Excel Know the Alphabet? (Excel 2K)

Just to make sure you are aware of this:
If intbins is over 26, you start getting into "other ascii" characters not letters, but keyboard symbols, and when it hits 97 you will get lowercase letters.

Steve

13. ## Re: Does Excel Know the Alphabet? (Excel 2K)

Yes, that will be part of my error checking earlier on...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•