Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    displaying numbers as letters (2000)

    Greetings,

    Is there a way to display a numeric value in a cell as an assigned letter? I don't want to actually convert the data, as there are calculations on the numbers. But I would like what displays to be something like "1" displays as "b", "2" displays as "c", "3" displays as a "p" and so on. I know you can do this in databases (give users a drop-down, and numeric values to their choices), but can't figure out if it's possible in Excel...

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: displaying numbers as letters (2000)

    You cannot do that with a custom number format, or only on a very limited scale.

    I'd use a column next to the numbers, with formulas to display the numbers the way you want (possibly using a custom VBA function). Use the original column for calculations (you can hide it if desired), and the new column for display.

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: displaying numbers as letters (2000)

    The obvious way to do this is to have two columns, one that is visible and contains a,b,c etc and one that is hidden containing 1,2,3 etc - the hidden column could easily be calculated as
    <code>=(CODE(A1)-CODE("a")+1)</code>
    assuming that the character is in cell A1

    StuartR

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: displaying numbers as letters (2000)

    Blast! I was afraid of that. Unfortunately I'm dealing with a huge matrix of values, so inserting hidden columns wouldn't be pretty.

    Thanks so much for the quick answer though; it saves me TONS of time going down that path!

    If you'd like to see what I mean, here it is: I'd like this to be as simple as possible, but it's trying to store a complex lot of data in an intuitive space. (Folks using it will be Excel newbies).

    (and if there's an easy way to add a 4th conditional format to the grid cells in Excel 2000, I'd be overjoyed to find it!)
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: displaying numbers as letters (2000)

    You could let the users enter letters (and provide a dropdown using Data | Validation), and convert them to the corresponding levels in the formulas to the right and below the matrix.

    Excel 2007 is the first version that supports more than 3 format conditions. You can use VBA code to provide extra format conditions, but that is not very attractive - it requires users to enable macros.

  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: displaying numbers as letters (2000)

    As a compromise you could make the default the cyan color of the beginner instead of yellow and distinguish as you have with a 0 for no training and a 1 for training. You could use custom formatting to make the zero a different color text to distinguish it a little more. The use the 3 conditions for the 2, 3,4 values of the other levels...

    Set Black text with Cyan background as explicit formatting [With format cells]
    Then with cond formating: 2 could be (Black text)/ green backgound, 3 (Black text)/ lavender background, and 4 (Black text)/orange background
    Then set custom format:
    [ red ][=0]0

    Then 1-4 will be black text with desired background
    and 0s will be red text with Cyan background...

    Steve

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: displaying numbers as letters (2000)

    I would be inclined to have two sheets--one hidden. Entering a number in the visible sheet would fire a macro that places the number in the hidden sheet, then replaces the number in the visible sheet with the appropriate letter and formatting. The hidden sheet would handle all numeric calculations. The foregoing updates one cell at a time for the convenience of speed; therefore I would recommend a button on the visible sheet which fires a macro to rebuild the visible sheet in total--just for maintenance purposes.

    H.T.H.
    Regards
    Don

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: displaying numbers as letters (2000)

    That would be really cool; but wouldn't the formulas - particularly on the last column - be insanely complex?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: displaying numbers as letters (2000)

    You could use a lookup table for the correspondence between the letters and levels, so that you don't have to use nested IFs, and you could use some auxiliary columns to the right of the last column, or adopt Don Wells's idea to use a hidden sheet.

  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: displaying numbers as letters (2000)

    That would work - but I'm really hesitant to open the macro door because of the number of users who will be copying and using this spreadsheet; most with minimal skills. To us, 'enabling macros' is straightforward, but it will probably be the tipping point for folks who aren't entirely excited about this idea in the first place..... The other thing that makes this complex is that there will be separate tab for each role; so this would mean twice the number of tabs in every workbook...

  11. #11
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: displaying numbers as letters (2000)

    Thanks; with your explanation of sumproduct in the other thread, I think I could figure out a lookup table... wish me luck!

Posting Permissions

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