| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell contents changing to date and not date
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell contents changing to date and not date
Try this:
Select the range in question EditReplace 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 "George Applegate" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell contents changing to date and not date
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, CopyPaste SpecialValues to "fix" the data -- Regards Roger Govier "George Applegate" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell contents changing to date and not date
Please don't multi-post.
-- David Biddulph "George Applegate" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
