Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
Cooking Tips
ExcelTips (menu)
ExcelTips (ribbon)
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
School Tips
Wedding Tips
WordTips (menu)
WordTips (ribbon)
Advertise on the
ExcelTips Site
Showing Filter Criteria on a Printout
Filling References to Another Workbook
Excel is normally pretty smart when it comes to importing data, but sometimes the automatic parsing it uses can be a real bother. For instance, you may import information that contains text strings, such as "1- 4- 9" (without the quotes). This is fine, but if you do a Replace to get rid of the spaces, Excel automatically converts the resulting string (1-4-9) to a date (1/4/09).
One potential solution is to copy your information to Word and do your searching and replacing there. The problem with this solution is that when you paste the information back into Excel, it will again be parsed as date information and automatically converted to the requisite date serial numbers.
The only satisfactory solution is to make sure that Excel absolutely treats the resulting strings as just that—strings—and not as dates. This can be done in one of two ways: just make sure that the original text begins with either an apostrophe or a space. This can be ensured by using the Replace feature of Excel (depending on the data you have to work with) or by using the Replace feature of Word (which is much more versatile).
With an apostrophe or space at the beginning of the cell entry, you can remove additional spaces or characters from the cell contents. If the result is text that looks like a date, Excel will not parse it as such because the leading apostrophe or space forces treatment as text.
Another way to perform the task is to follow these steps. (Assume that the original data is in the range A2:A101).
=SUBSTITUTE(A2," ","")
These steps work because the output of the SUBSTITUTE function is always treated as text. When you copy and paste text values, they are treated as text with no additional parsing done by Excel.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3019) applies to Microsoft Excel versions: 97 2000 2002 2003 2007
Your Data, Your Way! Want the greatest control possible over how your data appears on the page? Excel's custom formats can provide that control, and ExcelTips: Custom Formats can unlock the secrets to creating your own custom formats.