Cell contents changing to date and not date

  • Thread starter Thread starter George Applegate
  • Start date Start date
G

George Applegate

I have a problem.

In an excel spreadsheet I have a column that has part numbers, and is
formatted 'general'.

Some of the items would be
MAR-123465
MAR-168735
MAR-23148
MAR-2208
MAR-1938

Okay, here is the problem. If I do a replace and try to replace the
'-' in these fields with a blank (simply remove the dash) I end up
getting

MAR123467
MAR158735
MAR23148
Mar-08
Mar-38

When it removes the dash, it arbitrarily changes the format to
"custom" and date. What can I do to get it to simply remove the
dashes but let it show MAR2208 and MAR1938????

I'd appreciate any suggestions.

thanks,
ga

George Applegate
(e-mail address removed)
 
Try this:

Select the range in question
Edit>Replace
Find what: MAR-
Replace with: MAR
Click the Options button
On the "Replace with" line, click the Format button and set it to TEXT.
Replace All

Biff
 
Hi George

Assuming the data starts in A1
In a helper column enter
=SUBSTITUTE(A1,"-","")
and copy down
Mark the whole of the helper column, Copy>Paste Special>Values to "fix"
the data
 
Back
Top