Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a problem with formulas that is driving me nuts. I have a column of prescription numbers (not real prescriptions, no privacy is compromised:

    683327
    1268419
    1660633
    3646905A
    1660635
    1751421
    683327B
    3343724
    3554197
    683327A
    3646905
    4701974
    5042052
    990602887


    The pharmacist who asked for my help wanted to sort out those numbers ending with a letter. So I used the formula =RIGHT(A1,1). Excel displayed the numbers but not the letters. Those cells were blank. Then I tried retyping the formulas at the top, and now they just show the formulas. This problem has come up from time to time and it's never been a big deal until now. Maybe the workbook is corrupt; I just don't know. I have attached the workbook in case that will help. Thank you very, very much.
    Attached Files Attached Files
    Jim Whitt
    Pharmacist
    Temple, Texas

  2. #2
    New Lounger
    Join Date
    Apr 2010
    Location
    Michigan
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I looked at your attached spreadsheet and found a couple of issues:
    1) The cells with a value that ends with a letter also have spaces after the letter. This means your =RIGHT() formula returns a space.
    2) The reason your formulas are showing is that you have formatted the cells as "Text", rather than "General". When you format a cell as "Text" it shows exactly what you type in to the cell.

    Possible solutions:
    1) Instead of =RIGHT(A1,1), use =RIGHT(TRIM(A1),1). That will remove the spaces from the beginning and end of the string in the cell.
    2) You could use =ISNUMBER(A1) to do a boolean test on the value in the cell. (Or, =ISTEXT(A1) if you prefer to test the other...).


    edit: corrected formula examples

  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks very much. I'll check that out ASAP. Have a great day.

    Jim

Posting Permissions

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