With Excel there is always more than one way of completing a task. Previously I have written a post about splitting date and time from being combined in one cell. This was based upon using Excel Functions, LEFT, MID, RIGHT and then concatenating the result together, But, there is another way to do the same job without having to know Excel Functions. You can simply use the Text to Column feature that is standard in Excel!
In the image to the left you will see column A contains date and time combined together in each cell. Over the next few steps we will seperate the date and time into seperate columns. Column A will contain date and column B will contain the time. It is important to note that you ensure you have a spare blank column or two to your right, so you can fill it with the newly seperated data.
Selecting the Text to Columns Wizard from the Menu Bar
So first you need to highlight the cells that you want to convert to columns. There are two ways to do this, either select the entire column by clicking on the A button at the top of column. Or click in cell A1 and then with the mouse button still pressed, drag the mouse down to the last cell with data, in this case it’s cell A21. Now the cells are selected go to the menu bar at the top of screen and select Data > Text to Columns. Once you have clicked Text to Columns a pop up box will appear, this is the Convert Text to Columns Wizard (as shown in the image below).
The Three Steps to Using Text to Columns Wizard
Using the Excel Text to Column Wizard is very easy. Step one, ensure you have the tick box called Delimited ticked. It is important to have this one selected rather than Fixed Width as the delimited option is how you define seperating the text. The options include, commas, tabs, slashes, in fact almost any character. Once Delimited is selected click the Next button to progress to step 2.
The second step is to select how Excel will seperate the text. As mentioned in the previous step, this can be almost any character. But for this example we want to split the data where the space is between the date and the time. So tick the Space box on the form. Once you have clicked it you will see in the preview window that Excel shows you how the text will look split into columns. Be sure that the date is one column and the time in another. If you are happy that is seperated correctly then click Next.
Last step in the wizard. If everything looks fine here, then simply click finish. But you can select where the text will be enter. On the right hand side of the form is a tick box called Destination this allows you to manually pick a cell to start from. If you wish to do this simply click the small spreadsheet logo to the right of the box, the form will minimise and you can now pick a cell. Click the cell you want the data in, and then click the small spreadsheet logo again which should have an arrow pointing upwards. Finally click Finish and you should now have the date and time seperated into columns.
If after splitting the text to columns and the date still shows a time value, then highlight those cells, right click and select format cells. Then select Date. This will then format those cells as Date.