How to split text into rows and columns ?

This story will show how to separate text into rows and columns. The data I was working on was a mixture of words, characters and numbers. I have prepared a dummy dataset to explain the task. It is a very powerful and helpful method to clean text data.

Below is a screenshot of how the data looks. You can have a similar dataset with different characters, and you can experiment with your data using the same tools.

I have installed and done the setup of Kutools for excel.

Further, I will divide the task into steps.

Step 1: Select Merge & Split → Split Cells from Kutools.

Step 2: Split Cells → Split to Rows , as I want to split the text into rows first.

Step 3: Split by → Other , specify the character you want to split by.

Step 4: Select the cell and click Ok

Output :

Now, as I have done the splitting of text into rows, I want to separate the text and numbers into two different Columns.

Step 1: Select Merge & Split → Split Cells

Step 2: Select Split to Columns → Text and number → Ok

Output :

Now , Finally I want to remove the character { : } from the column F.

Step 1: Select Text → Remove Characters

Step 2: A Remove Characters window pops up. Select Custom → Add all the characters that you want to remove from the text.

Output :

Hope you find it helpful !!