Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    One column in my spreadsheet is formatted as text. The typical cell in this column contains (in order) a letter, a digit, a letter, and another digit. The data was pasted from a Word table, where the letters appeared in upper case. When I pasted the table, all the letters reverted to lower case. I thought it would be a simple matter to use the UPPER function, but so far I'm having no luck.

    The column in question is H. Cell H2 is the first that has any data; let's say it reads 3n5b. (Row 1 holds column headings). I select cell I2 and type: =UPPER(H2). (That's exactly what I read in Excel 2007 Help.) When I hit the <Enter> key, I expect to see 3N5B. Instead, I get exactly what I typed: "=UPPER(H2)."

    What am I missing?

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Using Excel 2002, which isn't fundamentally that different than 2007, I entered your formula, =UPPER(H2), into cell I2, and it worked as expected.

    I've had similar problems from time to time, which often were related to weird interactions between the formula and the cell formatting. What happens if you try changing the format of cell I2 from Text to General?

    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    As David Gray suggests, your cells are probably formatted as text. If you change the number format to General and enter the formula again (it's enough to press F2 then Enter), it should work correctly.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Yes! That's it! Thanks, guys!

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Ceasar,

    You are most welcome!

    I've had many such interactions, especially with cells formatted as Text. I suspect, though I cannot prove, that they are related to the absence of the leading tick (') used, following the example set by Lotus 1-2-3, version 1.0, to designate Text formatting of cells in computer generated worksheets. Fortunatly, in most cases, including the case at hand, you can safely dispense with Text formatting, falling back to General, which is the default.

    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    And all this time I'd thought 'text' would be the safe choice, just as plain text e-mail is supposed to be safer. We live and learn.

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Central Texas, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When you set the Text format, that's exactly what you get. Tick marks become visible and functions become visible. Numbers with leading zeroes keep the leading zeroes, anbd they get treated as text, not numbers.

    --Scott.

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbridge, VA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sometimes I run into problems getting Excel 2003 to accept what I type as a formula, even if the cell format is not text. The easiest way to get around this is to select the cells that you are having trouble with and then select Clear > All from the Edit menu.

Posting Permissions

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