OmniDocHow to cancel or delete Excel dates after they are automatically generated?
When inputting content such as 3-4 or 1/2 is automatically converted into a date by Excel, this article explains how to determine which situation is the case, how to change the generated date back to the original, how to avoid it in the future, and adds processing methods on mobile spreadsheet applications.
Key points of this article
- For cells that have been changed into dates, just changing the font is useless. You need to change the format and re-enter the original content.
- Set the cell to text format before inputting, or add English single quotes before the content to avoid being converted.
- Whether the edit bar displays a specific date is a direct way to determine the cell type.
- The entrance to the mobile form application varies from application to application. If you cannot find it, you can search for "format" or "text"
Just enter something like 3-4 or 1/2 in the Excel table, and the cells will automatically turn into dates, and the numbers or fractions you originally wanted to remember will suddenly be unrecognizable. Many people then want to cancel this automatic generation, or delete the date that has been changed, but find that just changing the content is useless, and the font cannot be changed back.
This article is written for anyone who encounters this problem on their computer or mobile phone. Let's first distinguish whether the problem is "Don't automatically convert it again in the future" or "These values need to be restored now", and then deal with it step by step according to the reason. After reading, you will know why changing the font does not work, how to restore the numbers and text to their original state, and where to start on your mobile phone.
The following operations are based on general Excel ideas. The interface names of different versions and applications may be slightly different, but the principles are the same.
The Excel table date is automatically generated, how to cancel it to make it effective? One sentence conclusion
Let me start with the conclusion: For cells that have become dates, you must first change the format to "regular" or "text", and then re-enter the original content; if you want to avoid it in the future, set the format before inputting, or add English single quotes before the content.
| your situation | Recommended practices | reason |
|---|---|---|
| Want to keep the content you enter in the future as is | Before inputting, set the cell format to text and then input | The format determines how the input is interpreted |
| The date has been generated and I want to restore it to the original numbers or text. | Change to regular or text format and re-enter the original content. | The essence of a date is a numerical value plus a display format. After changing the format, you need to re-enter it before it will be saved in the new format. |
| Just want to enter one or two values without conversion | Add English single quotes before the content, such as '3-4 | The single quotes tell the content behind the table to be treated as text and will not be displayed in the cell itself. |
| Encountered in mobile form application | Find the cell format or type option in the app, change it to text and then enter | The entrance varies by application, but the principle is the same |
First determine what kind of problem it is
Before taking action, make sure you know clearly which category the symptoms belong to, otherwise your work will be in vain.
| symptoms | Common causes | How to confirm |
|---|---|---|
| Enter 3-4 to display as date | The software recognizes input that matches the date format as a date | Click on the cell to see if there is a specific date in the edit column instead of 3-4 |
| The format has been changed and the date is still displayed. | The format has been changed, but the value in the cell is still a date and needs to be re-entered. | Change it to text and re-enter it to see if the display remains the same. |
| 001 becomes 1 | Leading zeros are removed from numeric inputs | Check if there is still 001 in the edit column |
| I changed the font but the date is still there | The font only affects the appearance and does not change the value of the cell. | Check whether the value changes after changing the format |
| After typing on the mobile phone, it becomes the date. | The mobile form application will also recognize input that matches the date style | Check the format or type of the cell in the app |
Check and repair item by item
The reasons for revising the law vary greatly, and are explained below according to the circumstances.
Already generated date data
- First confirm whether the date is stored in the cell. Click on the cell and look at the edit bar: If a specific date is displayed, the value has been converted.
- Select the area to be processed and change the cell format to text or normal. Note that changing the format only affects subsequent inputs and will not automatically restore existing dates.
- Double-click the cells with generated dates one by one to enter editing, delete the dates, re-enter the original content, and press Enter to confirm. This is straightforward when the quantity is small.
- When there is a lot of data, you can enter it in text format in the blank column first, and after checking it is correct, use "paste as numerical value" to put it back to the original position to avoid being converted again when pasting.
- If you find that the text has been converted just after typing, you can immediately use the undo shortcut key (Ctrl+Z for Windows, Command+Z for Mac) to return to the state before typing.
If a number such as 001 has lost the leading zero, it cannot be replaced out of thin air if the format is changed back. The number needs to be retrieved from the original file.
Mobile settings
- Open the table file, click on the cell where you want to enter data, and find the options related to format or number type.
- Set the cell or range as text and re-enter the content.
- If you cannot find the entry, you can search for "format" or "text" in the application's settings or help. If the mobile application is really difficult to operate, you can set the format on the computer first, and then view and supplement the content on the mobile phone.
How to avoid it happening again next time
- When entering numbers, fractions, phone numbers, and product codes in batches, set the entire column to text format before starting input.
- When entering numbers starting with 0 and long numbers, use text format uniformly to avoid losing leading zeros.
- After copying data from web pages, PDFs or other tables, first check the type of the first cell and confirm it is correct before batch processing.
- Before saving important tables, randomly check a few rows and compare them with the source data to see if they have been quietly changed.
- If the data originally came from a PDF table, re-fetching the data from the source file is often more reliable than manual patching. The PDF editor of the OmniDoc desktop client supports exporting to Excel, which can be downloaded from download.omnidoc.top. The format also needs to be checked after exporting.
❓ Frequently Asked Questions (FAQ)
1. After changing the font, why is the date still there?
The font only changes the appearance, not the value stored in the cell. To restore the content, you need to modify the format and re-enter it.
2. After changing the format to text, will the existing date be changed back?
Usually not. The format only affects subsequent inputs. Existing dates need to be re-entered one by one, or processed by pasting them as numerical values.
3. How to delete automatically generated dates and leave only blank cells?
After selecting the cell, press the Delete key to clear the contents. If you want to clear the cell format as well, you can use the clear format function and keep it blank.
4. What should I do if I can’t find the format settings on my phone?
The entry points for different mobile form applications vary greatly. You can search for "format" or "text" in the application's settings or help. When you really can't find it, it is recommended to set the format on your computer first, and then use your mobile phone to view and supplement the data.
📝 Summary
Excel table dates are automatically generated, and most of the time the input content is recognized as a date. First use the edit bar to confirm what is stored in the cell, and then use text format or single quotes to determine the subsequent input method, which can fundamentally solve the problem. The principle is the same on the computer side and the mobile side, but the entrance is different. If you check it in the order of this article, you can usually restore the table to the way you need.
