Text-to-Columns Function
The Text to Columns function will take text in one column and split it up into multiple columns. Using the Reformatted Date column from the previous exercise, follow the steps listed here to put each data element in its own column.
Step 2: Text to Columns instructions:
- Create three new columns next to Reformatted Date and title them Day, Month and Year (columns D - F)
- Click on C2, then do Ctrl (Cmd in Mac) + Shift + down arrow to select all the text date values
- Click on the Data Tab, then Text to Columns (near the Center of the Ribbon)
- The dialog box that pops up is a wizard that will walk you through some selections for creating your new columns. In the first screen, choose Delimited, then check the box by 'Space' (uncheck the others). Our values are delimited, or separated, by a space
- For each of the three new columns you can see in the Data Preview window, change the column data format from General to Text
- In Destination, click into the white box, then into the first field of the destination columns where this newly separated data will populate. This should be $D$2, the first field in the Day column. Then type : , delete anything that auto-fills, then type $F$4531, which is the last cell in the Year column.
- Click Finish
- The three new columns you created in your spreadsheet should now have filled with the Day, Month, and Year respectively from the original dates.
- As a final step, if you'd like to work with your newly separated dates as numbers, highlight cells D, E, and F from cell 2 to the bottom of the spreadsheet (highlight the first three rows, then do Ctrl (Cmd in Mac) + Shift + down arrow), click on the arrow to the right of the small diamond alert message that comes up, and from the options choose 'Convert to Number.'
