Join Dennis Taylor for an in-depth discussion in this video Use a Text to Column option for selected date formats, part of Excel 2016: Cleaning Up Your Data.
- [Instructor] In this worksheet called…Text To Column Dates, we see three columns,…columns A, C and E.…Each of them containing dates,…but in unusual formats.…As we look in column A, I think you quickly see…the four left most digits are likely…to be a year.…Depending upon the nature of the dates…and the scope, sometimes,…it could be a little confusing.…For example, you're looking at the entry…in row 8 there.…Is that a four digit year on the right?…Well based on the numbers on the left,…you'd say well neither one…of those could be a month…because they are greater than 12.…
So yes, that's not going to be the year.…The year is going to be…the four left most characters.…So usually, you can figure this out.…Although, on a given one at different times,…it might be a little tricky…as to where's the date.…And we've got the same kind of situation…in column C and in column E.…And each of these cases I think of the brief look…allows you to pretty much figure out…where the year is and then where the day is…and then where the month is.…
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Moving and inserting rows and columns of data with a simple drag
- Replacing data at the character level
- Converting dates with text functions
- Converting text data to values
- Using the CONCAT and TEXTJOIN functions to combine data
- Splitting data into columns via the Text to Columns feature
- Using Flash Fill for faster combining and splitting
- Checking and correcting spelling mistakes