by Excel is a helpful program when you have standard tasks and standard data. Once you want to go your non-standard-Excel way, some frustration is involved. Especially when we have large data sets. I came across one of such formatting issues when I dealt with our customers' tasks in Excel.
Surprisingly, it appeared to be quite a ubiquitous problem when we enter numbers with dashes or slashes, and Excel decides those are dates (or time, or what not). So, if you want to find the answer to the question: "Can you cancel automatic formatting?", it's a "No". But there are several ways you can deal with the format if it stands between you and your data.
Pre-format cells as text
It is really quite a simple solution that works when you are entering data into your sheet. To prevent auto-formatting, just do the following:
- Select the range where you'll have your special data. It can be a column or a number of columns. You can even select the entire worksheet (press Ctrl+A to do it straight away)
- Right-click on the range and select "Format Cells…", or press Ctrl+1
- Select Text in the Category list on the "Number" tab
- Click Ok
That's it; all the values you enter in this column or worksheet will retain their original view: be it 1-4, or mar/5. They are regarded as text, they are left-aligned, and that's all there is to it.
Tip: You can automate this task on both worksheet- and cell-scale. Some pros on the forums suggest that you can create a worksheet template you can use any time:
- Format worksheet as text following the steps above;
- Save as… - Excel template file type. Now every time you need text-formatted worksheet, you have it ready in your personal templates.
If you need text-formatted cells - create your own cell style under Styles on the Home ribbon tab. Created once, you can quickly apply it to the selected range of cells and enter the data.
Another way is entering an apostrophe (') before the value you are putting in. It basically does the same thing - formats your data as text.
Use data import wizard in Excel to open existing csv files
Solution #1 often didn't work for me because I already had data in csv files, the web, and elsewhere. You may not recognize your records if you try to simply open a .csv file in Excel. So this issue becomes a bit of a pain when you try to work with external data.
Yet there is a way to deal with this one as well. Excel has a wizard you can use. Here are the steps:
- Go to Data tab and find the first group on the ribbon - Get External Data.
- Click on From Text and browse for the file with your data.
- Use "Tab" as the delimiter. We need the last step of the wizard, where you can select "Text" in the "Column data format" section.
For more information, please see:
The bottom line: there isn't a simple answer that will let you forget about the format, but keeping in mind these two solutions will save you some time. Not so many clicks keep you away from your goal.
12 comments
Is there a fix for the 'Find and Replace' tool which auto formats Text fields to Numbers? I set the format of my columns as Text and even verify it with the ISTEXT function, immediately after removing "-" with the 'Find and Replace tool' it auto formats the column back to numbers. I'm working with FDA assigned numbers which have leading zeros, they are invalid without the zeros.
ie.. ISTEXT = TRUE on my formatted SKU's, after Find and Replace is ran to remove hypens, the ISTEXT is FALSE.
Hi! Try to remove extra characters using the SUBSTITUTE function. Then your digits will not be converted to a number.
=SUBSTITUTE(A1,"-","")
How do I turn off this ridiculously brain dead feature from affecting all future documents. I have a hard time wrapping my head around why this is the default.
Is there NO way I can tell Excel to not mess with the text in the CSV File on opening a CSV file directly?
This costs us hours of time and leads to major potential data issues. We lose leading zeroes on SKU codes, it converts GL codes to Dates, long numbers to scientific formulas - all with no warning. There needs to be a "Open without assessing data formats" option in excel. It boggles my mind that this has never been addressed or resolved by Microsoft.
Yes we can import the CSV file but I'd estimate that takes another 10 minutes per file (compared to often 1 minutes to open, save, re-import to our software - and when you're dealing with lots of them it is just not practicable (let alone the potential import creating corrupted data).
We've sent people to the moon. How is this STILL an issue.
Hello! Perhaps these recommendations will help solve the problem: Converting CSV to Excel: problems and solutions.
Doesn't work. Excel destroys the phone numbers, replaces the last 6 numbers with 000000.... :(
If you explain the problem in detail, we will try to solve it.
I have numbers in an excel sheet that are showing up as dates, but should be formatted to 'text'. i know how to format the cells to general or text, but when I change the format, it does change the number from a date, back to a number, but it's not the right number!
How do I get the cells to change the numbers back to the original text I entered?
Hi!
To show numbers or dates as text, use the TEXT function. Read more in this manual: Excel TEXT function with formula examples.
That's what i was looking for, a simple answer that thousands have asked.
Thank you for your feedback!
So does Excel still change two numbers separated by a slash (ex. 03/08) into a date format (08-Mar) when you export as a CSV? What does using the Tab as a delimiter have to do with keeping the format the way it is? I ran into this Excel defect over 10 years ago. Is Excel still broken in this way?