"Replace all" changes formatting

[Connie Martin]

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 Barrios]

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!

 

[Rick Rothstein]

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

 

[Kevin Barrios]

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

 

[Connie Martin]

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

 

[Connie Martin]

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