Separate text in Excel (Table of Contents)
Separate text in Excel using Text to Columns
We sometimes encounter situations where all the data is clubbed into one column, with each segregation in the data marked by some kind of delimiter such as –
- Comma – “,”
- Semicolon – “;”
- Space – “ “
- Tab – “ “
- Some other symbol
We could also have all the data in a single column with a fixed number of characters marking the segregation in the data.
When data is received or arranged in any of the formats shown above, it becomes difficult to work with the data because it is not formatted into proper row and column format. But if we see carefully, in the first screenshot, the columns (as it should be) are separated by semicolons – “;”, i.e. for the first row, the first column is the “First Name”, the second column is “Last Name” and the third column is “Age”. All the columns are separated by semicolons. This holds true for the rest of the rows. Therefore, we can split the data into proper row and column format on the basis of the strategic delimiters in the data. Similarly, in the second screenshot, we see that all the data has been clubbed into a single column. However, upon closer observation, we see that the columns (as it should be) can be differentiated on the basis of their lengths.
The first column is “Name”, followed by “Sales”. We see that the length of “Name” is 4 and the length of “Sales” is 5. This holds true for all the rows in the table. Therefore, we can separate text data in excel into columns on the basis of their Fixed Lengths. With Excel, we have a solution to these kinds of problems. Two very useful features of Excel are the “Text to Columns” or the “Split Cell” which helps to resolve these kinds of formatting issues, by enabling data re-arrangement or data manipulation/cleaning, since it becomes really difficult to work with a lot or all the data in a single column.
Note – There are several complicated formulae which can also achieve similar results, but they tend to be very convoluted and confusing. Text to Column is also much faster.
What is Text to Columns?
Typically, when we get the data from databases or from CSV or text sources, we encounter situations as shown above. To resolve these kinds of problems, we have a very handy feature in Excel called “Text to Columns”.
It can be found in the Data tab and then in the “Data Tools” section.
The shortcut from the keyboard is Alt+A+E. This will also open up the “Text to Columns” feature. Let us see some examples to understand how “Text to Columns” will solve our problem.
Examples of Separate text in Excel :
Example #1
Split First Name, Last Name and Age into separate text columns in excel (using delimiters) :
Let us consider a situation where we have received the data in the following format.
We have “First Name”, “Last Name” and “Age” data all clubbed into one column. Our objective is to split the data into separate text columns in excel.
To split the data into separate text columns in excel, we need to follow the following steps:
Step1 – We will first select the data column:
Step 2 – We will navigate to the “Data” tab and then go the “Data Tools” section and click on “Text to Columns”.
This will open up the “Text to Columns” wizard.
Step 3 – Now make sure that we click on “Delimited” to select it and then click on “Next”.
Step 4 – After this, in the next tab, deselect “Tab” first.
Then select “Semicolon” as the delimiter.
As soon as we select “Semicolon”, we see that the columns are now demarcated in the text preview. In the situation where there are multiple successive delimiters, we can choose to select the “Treat consecutive delimiters as one” option. Following that, we can click on the “Next” button.
Step 5 – Next, we shall look at the section where the column data format is described. We can choose to keep the data as either :
- “General” – This converts numeric values to numbers, date values to dates and remaining as text.
- “Text” – Converts all the values to text format.
- “Date” – Converts all the values to Date format (MDY, DMY, YMD, DYM, MYD, YDM)
- Ignore Column – This will skip reading the column.
Next, we shall look at the “Advanced” option.
“Advanced” provides us the option to choose the decimal separator and the thousands separator.
Next, we shall select the destination cell. Now, if we do not modify this, then it will overwrite the original column with “First Name”, the adjacent cell will become “Last Name” and the cell adjacent to that will become “Age”. If we choose to keep the original column, then we will need to mention a value here (which will be the next adjacent cell in this case).
After this, we shall click on “Finish”.
Our result will be as follows:
Example #2
Split Name, Sales into separate text columns in excel (using Fixed Width):
Suppose we have a scenario where we have data as shown below.
As we can see, the entire data has been clubbed into one column (A). But here we see that the format of the data is a bit different. We can make out that the first column (as it should be) is “Name” and the next column is “Sales”. “Name” has a length of 4 and “Sales” has a length of 5. Interestingly, all the names in the rows below also have a length of 4 and all the sales numbers have a length of 5. In this case, we can split the data from one column to multiple columns using “Fixed Width”, since we do not have any delimiters here.
Step 1 – Select the column where we have the clubbed data.
Step 2 – We will navigate to the “Data” tab and then go the “Data Tools” section and click on “Text to Columns”.
This will open up the “Text to Columns” wizard.
Step 3 – Now make sure that we click on “Fixed width” to select it and then click on “Next”.
Step 4 – In the next screen, we shall have to adjust the fixed width vertical divider lines (these are called Break Lines) in the Data Preview section.
This can be adjusted as per user requirement.
We need to click on the exact point where the first column width ends. This will bring the Break Line at that point.
Step 5 – Next, we shall look at the section where the column data format is described. We can choose to keep the data as either –
- “General” – This converts numeric values to numbers, date values to dates and remaining as text.
- “Text” – Converts all the values to text format.
- “Date” – Converts all the values to Date format (MDY, DMY, YMD, DYM, MYD, YDM)
- Ignore Column – This will skip reading the column.
Next, we shall look at the “Advanced” option.
“Advanced” provides us the option to choose the decimal separator and the thousands separator.
Next, we shall select the destination cell. Now, if we do not modify this, then it will overwrite the original column with “Name”, the adjacent cell will become “Sales”. If we choose to keep the original column, then we will need to mention a value here (which will be the next adjacent cell in this case).
After this, we shall click on “Finish”.
Our result will be as follows:
We can use the same logic to extract the first “n” characters from a data column as well.
Things to Remember about Separate text in Excel
- We should stop using complicated formulae and/or copy paste to split a column (separate the clubbed data from a column) and start using Text to Columns.
- In the Fixed-Width method, Excel will split the data based on the character length.
- In the Delimited method, Excel will split the data based on a set of delimiters such as comma, semicolon, tab etc.
- Easily access Text to Columns by using the Keyboard shortcut – Alt+A+E.
Recommended Articles
This has been a guide to Separate text in Excel. Here we discuss the Separate text in Excel and how to use the Separate text in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
