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.
20 comments
I am trying to do (what I thought should be) the simplest thing in the world, and somehow Excel messes it up no matter what I try. I want to enter a date manually and then drag it and get the following dates across a range. I want the date formatted according to a format that does not exist in the predefined Excel date formats - dd/mm. So starting with December 8, for example, I want to have 8/12 --> 9/12 --> 10/12, and so on. When I format cells as text and start with 8/12, 9/12 and drag it, I get 8-9-13, 8-9-14, etc. When I format it as date it automatically shifts to the formats that I do not want. Any ideas how to apply the continuous drag-add function only to the number on the left of a slash?
Hi! Here's a sequence of steps. 1. Write the date in the cell. 2. Set the custom date format to "dd/mm". 3. Drag down to copy.
In the latest version of Excel there is an option under File..Options..Data called Automatic Data Conversions and you can turn data conversions on or off (they're ON by default) for things like leading zeros, storing only 15 floating point decimals, auto conversion to scientific notation, auto convert continuous letters and numbers to dates.
I've fought this problem for ages and turning these options all OFF fixed it for most situations.
Thanks that's indeed the most useful tip.
The most useful suggestion. Fixed what I needed.
I am copying and pasting data published on the internet in the following form: 10/12 (i.e. made 10 of 12 shots). When asked how to interpret the text I have chosen both "Unicode text" and "Text" when doing a paste-special. If the numbers are clearly not a date (eg. 13/16), Excel presents it exactly as copied. However, editing a "45241" back to 12/12 is really frustrating. I am spending an insane amount of time trying to get the data corrected. I have no way of changing the way the original data is presented, and I have no interest in changing my sheet to pull the left 1 or two numbers and putting them in one scratch column, then doing the same thing with the numbers to the right of the "of" symbol (/)--especially when part of my data is correct and part of it is not. Any ideas to solve the issue. I have already considered =month(45241)&"/"&day(45241). If all my data was like that, then no problem. When the ratio is close t0 50/50, then not so much.
Hi! I followed the instructions in the article above and unfortunately I was unable to replicate your problem. I can assume that you are not pasting data from a file, but copying it from a website. Try setting the cells to text format and then paste the data into them via Paste Special. Or save the data to a text file and follow the instructions in the article above.
Not a satisfactory workaround - as soon as you add a cell, row or column, it adds it as general despite the rest of the sheet being text.
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?