Results 1 to 13 of 13

20031020, 19:42 #1
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
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?
[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20031020, 19:48 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
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.

20031020, 20:06 #3
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Re: Does Excel Know the Alphabet? (Excel 2K)
nope, that will work ...
[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20031021, 01:45 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>
Legare Coleman

20031021, 10:04 #5
 Join Date
 Jul 2002
 Location
 London, United Kingdom
 Posts
 192
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 az . 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

20031021, 10:36 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Does Excel Know the Alphabet? (Excel 2K)
If you only have 26 you can use
=CHAR(96+A1) will get you "az"
=CHAR(64+A1) will get you "AZ"
If you want to to get those 26 + the 676 2 digit (702 total):
=IF(A1<=26,"",CHAR(INT((A11)/26)+96))&CHAR(MOD(A11,26)+97) for "azz"
=IF(A1<=26,"",CHAR(INT((A11)/26)+64))&CHAR(MOD(A11,26)+65) for "AZZ"
This assumes you start in A1, copy as appropriate.
Steve

20031021, 10:44 #7
 Join Date
 Jul 2002
 Location
 London, United Kingdom
 Posts
 192
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20031021, 11:17 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20031021, 11:35 #9
 Join Date
 Jul 2002
 Location
 London, United Kingdom
 Posts
 192
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20031021, 14:40 #10
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.Legare Coleman

20031021, 15:54 #11
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
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.[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20031021, 16:09 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20031021, 16:12 #13
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Re: Does Excel Know the Alphabet? (Excel 2K)
Yes, that will be part of my error checking earlier on...
[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile