Text to Columns in Excel are used to split a single column of text into multiple columns. This feature is helpful when we import data from word, PDF or any other source. As the imported data may not be usable right away. We can split the text based on fixed width or split it at each comma, or at period or at any other character. For example, assume we have a list of data with Names and their Designation as shown in the image below.

We are interested in splitting the data into First name, Last name and Designation. This blog explains how to split text to columns in excel.

Step 1: Select data

Once we have the data, select the range.

Step 2: Split text to columns

After selecting the range, go to Text-to-columns. This function is available in Date Tools section in Data tab in Excel ribbon as shown in the image below.

Now, we get a pop up window which is a 3-step wizard. In first step, we select ‘Delimited’ as our text is not having fixed width. After selecting the option, click Next.

Step 2 – In wizard, we select Delimiters as per our requirement. In this example, we selected Space and Other (Enter dash in other field) as Delimiters as shown in the image below. Data Preview in the wizard window will show how the text will be split based on the delimiters selected. Now, click Next.

Step 3 – In wizard, we can set the data format for each column. In this example, we selected ‘General’ as data format. The destination is cell ‘$A$1’, we can assign the cell address based on our requirement. Now click ‘Finish’.

Note that if destination is not specified then the data which has been split will replace the original data.

As we can see from the image below, the text has been split to columns.

This is how we use Text to Columns in Excel. If you are interested in splitting the text in VBA then, go to the blog over here. Have any questions? Feel free to contact us here.