formatting question

H

Helen

Hello,

I have received a workbook from a vendor that lists UCC codes in the
following way:

(00) 0 0660142 02770000 1

I need to store it without the spaces and the brackets; 0000660142027700001

So I use the Find and Replace feature and take the brackets out. That works.
But when I take out the spaces I get the following result in the cell:

6.60142E+15

In the function bar the number now is: 6601420277000000

What am I doing wrong? I have tried formatting the field as Text first, but
with the same result.

Thanks,

Helen
 
D

Dave Peterson

Excel only keeps track of 15 significant digits.

Maybe you can use a helper cell and use something like:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),")",""),"(","")
 
H

Helen

Brilliant!

Works great.

Thanks,

Helen


Dave Peterson said:
Excel only keeps track of 15 significant digits.

Maybe you can use a helper cell and use something like:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),")",""),"(","")
Click to expand...
 
E

edcosoft

Put a ' in front of the number making it text. Or put a space in
front of the number. :ed
 
Top