Results 1 to 13 of 13
  1. #1
    Super Moderator WebGenii's Avatar
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    Super Moderator WebGenii's Avatar
    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

  4. #4
    Uranium Lounger
    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

  5. #5
    2 Star Lounger
    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 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. #6
    WS Lounge VIP sdckapr's Avatar
    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 "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. #7
    2 Star Lounger
    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.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    2 Star Lounger
    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

  10. #10
    Uranium Lounger
    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

  11. #11
    Super Moderator WebGenii's Avatar
    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

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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

  13. #13
    Super Moderator WebGenii's Avatar
    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

Posting Permissions

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