formatting question

[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

[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," ",""),")",""),"(","")

Helen wrote:
>
> 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

--

Dave Peterson

[Helen]

Brilliant!

Works great.

Thanks,

Helen


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Excel only keeps track of 15 significant digits.
>
> Maybe you can use a helper cell and use something like:
>
> =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),")",""),"(","")
>
> Helen wrote:
>>
>> 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
>
> --
>
> Dave Peterson

[[email protected]]

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