Splitting Cell Entries

Sometimes you may have a spreadsheet where several items of data have been entered in one field, e.g. a person?s first name and surname.  While it is possible to write a fairly complicated text formula to split the name into its two parts, Excel's Text to Columns  feature can split your data very easily.

If you have a column containing data that you want to split, e.g. full names, highlight that column by clicking on the column letter at the top of the worksheet and then click on the Data drop-down menu and choose Text to Columns.  The first step of the wizard should appear as below and you can choose whether there are any delimiters (separating characters) in your data or whether you want Excel to split it by taking fixed length chunks of data into separate columns.

When you have chosen how to separate your data, click Next and you will be taken to the next step of the wizard.

Choose the delimiter that is separating your data, in our example it is a space.  If there is a tick in the Treat consecutive delimiters as one box then Excel will ignore multiple spaces between words when splitting them.

If you have chosen to use a fixed length method of splitting your data you will see a screen as shown below where you can set the lengths you want to split out.

When you click Next you will be able to click on the newly formed columns and choose a format if they need formatting.  Make sure you use the Destination box to set the top left corner of the area where you want the new columns otherwise you may overwrite your existing data.  If you click on the Advanced button on the window shown below you can also choose what type of decimal and thousands separators you want to use.



When you are happy with this, click the Finish button and your data will be split across the columns of your worksheet.

Click here to return to the Excel index.