Split Excel Data Using Text To Columns


The Excel text to columns function is one great way to split simple cell content such as address line data or first names and last names to multiple cells to enable you to analyse or sort your data more effectively.

You will mostly need to do this when you copy data from another application or receive data dumps from other data sources, or inherit data. But, with the click of (a few) keys we can get over this little inconvenience.

First of all you need to insert extra columns to the right of the data you want to split if your column full of data is in the middle of your data sheet- if its not you don’t need to as it will automatically post your new data left to right.

  1. Highlight the column of data
  2. Data
  3. Text to columns
  4. Delimited or Fixed Width
  5. FIXED WIDTH- use this when you have really uniform data ( I am never lucky enough) to manually drag the line along your data to split it
  6. In this example we are using DELIMITED

7. Select the syntax to split your data i.e comma, space tab etc, in my example I am using comma

8. Next
9. Hey- your data is split- you can work away on your analysis now…..

Let me know how you find the text to columns- how were you previously splitting your data?

Other Excel Tips You Might Like.

1. Data validation using a dynamic list

2. Format only the cells that contain text in Excel

Feel free to share your comments and suggestions to other our other “Excelers”


Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts