The original cell contains numbers with quotes, e.g. '12345' which of course makes it text. I use =REPLACE to remove the quotes, leaving just 12345, which is a number, but still stored as text - even after formatting the cell as a number.
I need to look it up on another sheet which has the same number stored as a number, and =VLOOKUP can't find it! On my work laptop, running Vista and Excel 2003, I can copy the number into NotePad, and copy it back again to find it's magically converted to a number stored as text, and =VLOOKUP works.
I tried the same thing with Lion, Excel 2011, and TextEdit, and it didn't work - the number is still a number. I tried reformatting the cell as text, and the number jumps to the left, so it looks like text, but =VLOOKUP still can't find it.
I need something as simple as NotePad - it seems TextEdit is too clever!
I need to look it up on another sheet which has the same number stored as a number, and =VLOOKUP can't find it! On my work laptop, running Vista and Excel 2003, I can copy the number into NotePad, and copy it back again to find it's magically converted to a number stored as text, and =VLOOKUP works.
I tried the same thing with Lion, Excel 2011, and TextEdit, and it didn't work - the number is still a number. I tried reformatting the cell as text, and the number jumps to the left, so it looks like text, but =VLOOKUP still can't find it.
I need something as simple as NotePad - it seems TextEdit is too clever!