software

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
Excel only keeps track of 15 significant digits.

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

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,quot; quot;,quot;quot;),quot;)quot;,quot;quot;),quot;(quot;,quot;quot;)

Helen wrote:
gt;
gt; Hello,
gt;
gt; I have received a workbook from a vendor that lists UCC codes in the
gt; following way:
gt;
gt; (00) 0 0660142 02770000 1
gt;
gt; I need to store it without the spaces and the brackets; 0000660142027700001
gt;
gt; So I use the Find and Replace feature and take the brackets out. That works.
gt; But when I take out the spaces I get the following result in the cell:
gt;
gt; 6.60142E 15
gt;
gt; In the function bar the number now is: 6601420277000000
gt;
gt; What am I doing wrong? I have tried formatting the field as Text first, but
gt; with the same result.
gt;
gt; Thanks,
gt;
gt; Helen

--

Dave Peterson

Brilliant!

Works great.

Thanks,

Helenquot;Dave Petersonquot; gt; wrote in message
...
gt; Excel only keeps track of 15 significant digits.
gt;
gt; Maybe you can use a helper cell and use something like:
gt;
gt; =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,quot; quot;,quot;quot;),quot;)quot;,quot;quot;),quot;(quot;,quot;quot;)
gt;
gt; Helen wrote:
gt;gt;
gt;gt; Hello,
gt;gt;
gt;gt; I have received a workbook from a vendor that lists UCC codes in the
gt;gt; following way:
gt;gt;
gt;gt; (00) 0 0660142 02770000 1
gt;gt;
gt;gt; I need to store it without the spaces and the brackets;
gt;gt; 0000660142027700001
gt;gt;
gt;gt; So I use the Find and Replace feature and take the brackets out. That
gt;gt; works.
gt;gt; But when I take out the spaces I get the following result in the cell:
gt;gt;
gt;gt; 6.60142E 15
gt;gt;
gt;gt; In the function bar the number now is: 6601420277000000
gt;gt;
gt;gt; What am I doing wrong? I have tried formatting the field as Text first,
gt;gt; but
gt;gt; with the same result.
gt;gt;
gt;gt; Thanks,
gt;gt;
gt;gt; Helen
gt;
gt; --
gt;
gt; Dave Peterson
Put a ' in front of the number making it text. Or put a space in
front of the number. :ed