Unclean or Invalid Dates (DMY Format) should be converted to valid data formats.
By default, Excel accepts date input in MM/DD/YY format (US format) unless you change the control panel settings of your PC. Example 22.10.2007 or 22/10/2007 date may be considered invalid.
Now Splitting name into two columns as the First Name and Last Name is easy. Read the Case study and examples based on it. Click now:
Important Note: Technically, every valid date is a number in Excel.
Excel Vlookup formula – Guidebook
Bored of downloading text heavy / copy-pasted eBooks?
If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.
Let’s see how Text to Columns can help clean date formats. Assume you have a recordset consisting of a large number of dates like below:
Now look at the very first date – Is it 8th April or 4th August? Confusing, right?
A lot of users select the entire range and then go to Format Cell and then change the format of the Date. But it will not change anything as it is not a valid date format in Excel since dot separators are invalid. If it is not a date, then it’s a simple text to excel. Now how will you fix the issue?
From the above table, if you look at the 4th row then you can understand that it is 25th July. To make it a valid date you need to flip the position of 25 with 7 i.e. 7 should be first and then 25. Additionally, the separator should be a slash (/). Will you re-enter dates one by one manually? Very boring task!
Also, now you can, ‘Transfer Single Columnar Data into Multiple Columns. To know more click here
15 Excel Data Cleaning Tricks Guidebook
Bored of downloading text heavy / copy-pasted eBooks? If Yes, you will enjoy this guidebook (43 pages)
Here’s what you need to do.
Step 1: Add an extra column (Remarks). Then ask a question to Excel if the date given in the first column is a number. You can use ISNUMBER formula. Since every valid date is a number, TRUE indicates, a date is a number and hence, possible a valid date.
Now after applying ISNUMBER formula, you are getting results either TRUE or FALSE. You don’t need to change the date where you are getting TRUE (read: valid dates).
Step 2: Sort the data table with respect to the 3rd column (Remarks), so that TRUE and FALSE are sorted in a separate block.
Excel Vlookup formula – Guidebook
Bored of downloading text heavy / copy-pasted eBooks?
If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.
Now, this give will you true records on one side and false on another side.
Step 3: Now select only the dates against which the remarks are false.
Step 4: Go To Data tab and then Text to column. Choose a Delimited option. Click on Next
Step 5: Ensure that none of the checkbox is Switched ON in Step 2 of 3. Click on Next.
Step 6: When you are in Step 3 of 3 of Text to Columns wizard, then please stop here for a moment. You have entered the wrong date in Excel, and you need to confess it to Excel. Where is the “confession box”? In Step 3, An option button called Date. Choose the format of the current status/sequence of date format. It is DMY (8.4.2007) in our case. Click on Finish.
Step 7: MS Excel will convert the invalid dates into the valid ones. Isn’t it really cool? These sorts of date dumps are often seen in ERP reports of SAP, Oracle, Ramco, Tally, etc. There are advantages of having a clean date and one such advantage is that you can easily use Pivot Table’s Date grouping feature. Click here to know more about it.
Conclusion
There are times when there is a lot of information in one text cell and one cannot view all the text in one go. In such cases, splitting the Texts into Columns is a must to know the data cleaning technique. This technique basically brings clarity to the text. We have shown you advanced tricks of Text to Columns. This technique can also be used to import data files such as CSV, TXT.
Bonus: An alternative to Text to Columns was introduced in Excel 2013 version. You can check out how the FlashFill feature can be used to clean the data even faster.
15 Pivot Tables Tricks for Pros
15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.
Most Popular Tricks are #3, #7 & #12