Join Dennis Taylor for an in-depth discussion in this video Split unusual date formats into columns, part of Excel 2016: Cleaning Up Your Data.
- [Instructor] Sometimes when you get information…from other sources,…the dates are put in in an unusual way…and sometimes incompletely.…For example, in this worksheet called DateFormats…in our chapter three file,…we've got some entries in column A, also in column C,…that are similar, they're in reverse order.…They've got month and year…but not a day associated with them.…And over in column E we've got all three of them…but in a different layout.…The key to converting these many times…is a combination of using a function called DATE…as displayed in this generic form here in cell B1…and also some of the text functions,…like left and right,…that allow us to extract data from the left side…or right side of a cell.…
So, in cell B2, we want to construct,…using the DATE function and some other text functions,…a viable date,…so that we can actually work with it…in formulas and also in formatting as we work with Excel.…Dates like this we can't do a lot with…in any kind of computational sense.…So, let's convert this into a date that's workable.…
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