Separating Text

Sometimes we have a lot of data that we want to separate into different columns and doing it one by one will take a lot of time. Here I will be showing you how you can separate text into different columns, just follow the few steps below to learn this.

SEPARATING TEXT
Step 1 - In this tutorial, I will be using the excel worksheet below to teach you how you can separate text. In the excel worksheet, we will be separating the Full Name column into the First Name and Middle Name column.

Step 2 - Select the data (i.e. the names itself), then click on the Data tab. Go to the Data Tools group and click on the Text to Columns option.

Step 3 - A dialog box will be displayed. In this dialog box, just make sure that the Delimited option is selected, inside the Original data type section. Then click on the Next button.

Step 4 - In this next place, choose the preferred, delimiters you want to use: space, tab, comma etc. But the space delimiter should be okay. Then click the Next button.

Step 5 - In this next place, select the column format that you want to use for the columns, normally we are separating text so select the General or Text format.

Step 6 - Before clicking on the next button, if you click on the Advance another smaller dialog box will be opened where you can set advanced import settings. For text separating you do not need to do anything here, but if you are working with number, then you can set for decimal and thousands' separators. So just click on the OK or Cancel button to take you back to the previous dialog box.

Step 7 - Next is to choose the cell where we want our separated text to start from. If you at the destination, it is still on the selected cell column (Full Name - $A$2). So, click on the icon at the ending of the destination input field as shown in the image below.

Step 8 - After clicking on it. Click the cell to select the destination where you want the separated text to start from. Notice as soon as you click the cell, the destination name will appear in the destination input field.
To bring back the full dialog box again and continue, click on the icon at the ending of the destination input field as shown in the image below.

Step 9 - The start destination has been changed from the Full Name column ($A$2) to First Name column ($B$2). Then click on the Finish button.

Step 10 - The text will be separated successfully to the First Name column and the Middle Name column respectively.
But notice that the text does not have the same formatting as the Full Name column. So, what do we do here?

Step 11 - Select the data in the full name column that has the formatting that you need. Then in your Home tab, go to the Clipboard group which is the first group there and click on the Format Painter icon.