Excel has this feature where it can automatically convert the number in a cell to a specific date. But, this useful feature can quickly become the cause of your headace if the Excel starts to change the numbers to the date format! This problem may affect the entire row or column. Irrespective of the situation you are facing, these tips and tricks should help you troubleshoot the problem.
Fix 1 – Format the cells as text
You must format the cells in the Excel spreadsheet as text. This will fix the problem for sure.
Step 1 – Open the Excel workbook where you have faced the issue.
Step 2 – Then, select the problematic cells in there.
Step 3 – Next, right-click the selected cells at once and choose the “Format Cells” option.
Step 4 – You must be present in the “Number” section.
Step 5 – Set the ‘Category:’ to “Text” from the list.
Step 6 – Tap “OK“.
This way, you can designate these cells to contain only text. After doing these changes, there won’t be any issues regarding cells changing to date autoamtically.
Fix 2 – Paste the text as special text
If you are pasting the text from a different data set/workbook or any other source, you should prefer the paste special opion.
Step 1 – While pasting the copied contents, find the Paste settings.
Step 2 – Tap “Paste” and open the “Paste Special” option.
Step 3 – As this loads the default Paste Special page, choose the “Text” or “Value” option from there.
Step 4 – Click “OK“.
Insertig the values in the cell the specified way should help you fix the issue.
The numbers won’t get automatically turn into the date format anymore.
Fix 3 – Put an apostrophe
There is a shortcut fix as well. Putting an aphostrophe before the value in the cell should fix it.
Step 1 – In the workbook, go and select the cell.
Step 2 – Next, put this ‘ in the cell.
Step 3 – Paste the date in there just after the apostrophe sign.
As Excel won’t be reading the cell data as the date input, it won’t automatically transfrom it into the date.
That’s it! The data you put after the apostrophe sign will not be automatically converted to the date format.
Hope these solutions have helped you solve the issue.