Stopping Excel Converting text with leading blanks/zeroes to a number
Posted by anthony hammond on October 02, 2001 6:47 AM
Hi, Excel seems to treat blanks and zero as special, and decides that if the "text" following these is a number, it will treat it as a number rather than text. I bet the answer's obvious. Thanks in advance
I have data formatted
I want to strip the letter (its always the same one so I can do a replace), but leave the blank (or zero).
Example:
input
x<blank>123
x0456<br>x789
Output
<blank>123
0456<br>789
Posted by Mark W. on October 02, 2001 6:51 AM
Format these cells as "Text" before you replace
the "x" with "".
Posted by MikeB on October 02, 2001 7:00 AM
Mark W,
Try this....
If your input datum is in cell A1, placing the following formula in cell B1 should give you the data that you want...
=RIGHT(A1,LEN(A1)-1)
The output is text, not number, format.
All the best, MikeB
Posted by anthony hammond on October 03, 2001 1:59 AM
Thanks to Mike and Mark.
The formula Mike mentioned works. But I forgot to mention that the x can appear anywhere in the string not just at the begining. However I can use the principle of the formula and add something to it to locate the position of x.
Making the cell Text doesn't work (in my version).
We've also found other problems where *sometimes* Excel is displaying text cells with numbers by rounding them - the total number of characters being 11, 10 digits plus a decimal place, although the actual data value in the cell has many more digits.
So we've given up on Excel for now (too unpredictable) and are using Word!
Once again thanks for the help.
Anthony
Posted by Aladin Akyurek on October 03, 2001 2:33 AM
> The formula Mike mentioned works. But I forgot to mention that the x can appear anywhere in the string not just at the begining. However I can use the principle of the formula and add something to it to locate the position of x.
Use rather:
=SUBSTITUTE(A1,"x","")
which also delivers text-formatted output.
Aladin
