How To Use Excel Text To Columns Wizard To Convert Simple (Consistent) & Complex (Inconsistent) Date Format Text To Date

In the previous article, I told you how to convert text to date in Excel using Excel functions and formulas. In this article, I will tell you how to convert text to date in Excel using the Excel Text to Columns wizard. Using this wizard, you do not need to know any Excel functions and formulas.

1. How To Use Excel Text To Columns Wizard To Convert Simple Date Format Text To Date.

1. Suppose there is a column in your Excel worksheet, and all the cells in the column are text format date values, and the text data use the same format pattern such as yyyy-mm-dd.
2. Below is the example column with text format date value in the column cells.

   2020-01-01
   2020-01-02
   2020-01-03
   2020-01-04
   2020-01-05
   2020-01-06
   2020-01-07
   2020-01-08
   2020-01-09
   2020-01-10

3. To convert the above column of text format date to Excel date value, you should click the first cell, then press the Shift key and click the last cell. This action will select all the cells in the column.
4. Then click the Excel ribbon Data tab, in the Data Tools group click the Text to Columns icon button.
5. Then it will pop up the Convert Text to Columns Wizard.
6. Select the Delimited radio button in the Original data type area, and click the Next button.
7. On the second wizard page, uncheck any checkbox in the Delimiters area, and click the Next button.
8. It will open the third wizard page, you should select the Date radio button in the Column data format area.
9. Select the date text format from the drop-down list after the radio button.
10. Because in this example, the date text format is yyyy-mm-dd, so we select the YMD item from the date format drop-down list.
11. Click the Finish button on the last wizard page, and the Convert Text to Columns wizard dialog will be closed.
12. We can see that all the date format text has been changed to the Excel date value, and they are aligned to the right side of the cell.
13. And the Number Format value has also been changed from Text to Date.
14. You can see this in the Home tab Number group, Number Format drop-down list.
15. If you select the General item from the Number Format drop-down list, you can see the number value of the Excel date in the cell.
16. This is because all the Excel date value is stored as an integer number inside.
17. And the number 1 represents the date 1900-01-01.

2. How To Use Excel Text To Columns Wizard To Convert Complex Date Format Text To Date.

1. If each cell in the column saves different date format text, we can also use the Text to Columns wizard to convert the text to Excel date value.
2. Below is the example text column, each cell uses a different delimiter to separate the year, month, and day number values.

   2022.10.10
   2021-9-9
   2022 Aug 1

3. First, we should click the Home tab, and click the Find & Select —> Replace… menu item in the Editing group to open the Find and Replace dialog window.
4. Then input the character – in the Find what text box, and input character dot(.) in the Replace with text box, click the Replace All button to replace the – character in the date text with the dot (.) character.
5. Repeat step 4 to replace the space character with the dot( . ) character.
6. After the above process, we can get the below column, all the cell’s text in the column uses a dot(.) as a date text delimiter.

   2022.10.10
   2021.9.9
   2022.Aug.1

7. Now the date text format in each cell is almost consistent and you can follow section 1 to convert the text to date in Excel using the Text to Columns wizard.

References

1. Excel Convert Text To Date Example.