I have formatted a column to text. Currently in the column is a list copy
and pasted from MAS 500. Contents of each cell look something like this:
SO-0000001016. With the column formatted as text, I did Edit/Replace,
replacing SO- with nothing, intending to remove simply the SO- and leave the
six zeros. However, it wipes out the six zeros, as well, leaving simply
1016. If I manually remove SO- on every number, the six zeros remain. What
gives?

Yes, Excel is a great document tool but it has its limitations as well. The
zeros before a number value has always been a little fussy. For example, zip
codes on the east coast.

In order to preserve your data with minimizing re-typing in numerous fields,
this is the "work around". There may be a more elegant solution, but this is
the only way I know how to get what you want done, done.

1. Your MAS-500 is most likely outputting the file to a .txt or .csv or some
other simple text file. Open up that file, select everything and then just
put it in a Word document (use paste special, unformatted text)

2. In Word, do the find/replace and replace the SO- with "nothing"

3. Copy all of the data onto the clipboard

4. Open up an Excel file and format a column to "text"

5. Use Paste Special, Text, and copy into the column.

------------------

Text formatting is one of the most common ways to preserve zeros before a
number. However, this will have limitations as it is a text value and not a
numeric value. In order to preserve a numeric value with zeros in front,
another option is to use the "custom" format and just put in 10 0's in your
case.

Hope this helps, cheers!

Still using SO- in the "Find what" field... instead of using nothing in the
"Replace with" field, try using an apostrophe (') instead.

--
Rick (MVP - Excel)


"Connie Martin" wrote in message
...
I have formatted a column to text. Currently in the column is a list copy
and pasted from MAS 500. Contents of each cell look something like this:
SO-0000001016. With the column formatted as text, I did Edit/Replace,
replacing SO- with nothing, intending to remove simply the SO- and leave
the
six zeros. However, it wipes out the six zeros, as well, leaving simply
1016. If I manually remove SO- on every number, the six zeros remain.
What
gives?

ah snap, forgot to mention that if you're going to use the custom format and
paste into it, you'll have to use edit, paste special, values to preserve the
formatting

You know what? I had thought of that, but because I have LOOKUP's in this
spreadsheet I didn't know if '0000001016 would be seen the same as
0000001016, which is in another worksheet from where it does the lookup. I
should've tried it because it works! Thank you! Connie

"Rick Rothstein" wrote:

Still using SO- in the "Find what" field... instead of using nothing in the
"Replace with" field, try using an apostrophe (') instead.

--
Rick (MVP - Excel)


"Connie Martin" wrote in message
...
I have formatted a column to text. Currently in the column is a list copy
and pasted from MAS 500. Contents of each cell look something like this:
SO-0000001016. With the column formatted as text, I did Edit/Replace,
replacing SO- with nothing, intending to remove simply the SO- and leave
the
six zeros. However, it wipes out the six zeros, as well, leaving simply
1016. If I manually remove SO- on every number, the six zeros remain.
What
gives?


.

Kevin, thank you for replying. Your method would work too, but I'll go for
the shorter one. Appreciate your input. Connie

"Kevin Barrios" wrote:

ah snap, forgot to mention that if you're going to use the custom format and
paste into it, you'll have to use edit, paste special, values to preserve the
formatting