How to Split Text Data of a Cell to Column in Excel

In our previous post “10 Basic Excel Functions That Everyone Should Know” we learned how to use the =CONCATENATE() function. Concatenate merges data of different cells into one cell.

What if I want to split the data of a cell into multiple cells or in other words how to reverse the concatenate function?

For example, I have names of 3 fruits separated by a comma in A1. How can I want to split all three names of fruits neighbouring cells?

Data I have:

A1: Apple, Orange, banana

Result I want:

B1: Apple

C1: Orange

D1: Banana

We can easily reverse the Concatenate Function by using “Text to Column” in Data Tools category under the Data Tab, which is a built-in function of Excel.

The Text to Columns feature is what we can call as the opposite of the concatenate function. Applying it to data will split cell values into multiple cells with any required separators.

Follow below steps to learn how to use “Text to Column” function by using the above example of names of fruits:

Step 1: Select the cell A1 which contains names of fruits separated by a comma.

Apple, Orange, banana

Step 2: Click the Data tab and select Text to Columns option in Data tools category as shown in below screenshot:

Step 3: Wizard for Text to column will open as shown below:

Step 4: In the Text to column wizard, select the Delimited option as shown below and click next:

Step 5: In this step, we can specify the separator that we want to use under the Delimiter option as shown below and click next:

Step 6: We have reached the last step, Step 3 of the wizard. In the Column data format, put a check on General and in the Destination select a cell you require the output to be displayed.

You can either type manually by putting a $ sign as shown below or click on the box next to destination tab and directly select the cell. In second option Excel puts a $ sign for you.

In our example we have selected B1 as shown in the screenshot below and click Finish button:

Clicking Finish will deliver the output in the sheet. We can see all names split into multiple columns, as we can see in the screenshot below:

Don’t you think it was pretty easy to reverse the concatenate function in just six steps?

Encourage me to write more and more by sharing your comments and queries. I will be happy to reply.