XL 2007 – formulas do not always display result

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

Thanks very much. I’ll check that out ASAP. Have a great day.

Jim

Reply | Quote