Trending September 2023 # Separate Text In Excel (Examples) # Suggested October 2023 # Top 16 Popular

You are reading the article Separate Text In Excel (Examples) updated in September 2023 on the website Speedmintonvn.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Separate Text In Excel (Examples)

Separate text in Excel (Table of Contents)

Introduction to Separate Text in Excel

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 because it is not formatted into a proper row and column format. But if we see carefully, in the first screenshot, the columns (as they 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”. Semicolons separate all the columns. This holds for the rest of the rows. Therefore, we can split the data into a proper row and column format based on the strategic delimiters in the data. Similarly, the second screenshot shows that all the data has been clubbed into a single column. However, upon closer observation, we see that the columns (as they should be) can be differentiated based on 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 based on 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 of all the data in a single column.

Note: Several complicated formulae can also achieve similar results but 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, CSV, or text sources, we encounter situations as shown above. We have a very handy feature in Excel called “Text to Columns” to resolve these kinds of problems.

It can be found in the Data tab and 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

Below are the different examples of separating text in Excel:

Example #1

Split First Name, Last Name, and Age into separate text columns in Excel (using delimiters) :

You can download this Separate text Excel Template here – Separate text Excel Template

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:

This will open up the “Text to Columns” wizard.

Step 4 – After this, in the next tab, deselect “Tab” first.

Then select “Semicolon” as the delimiter.

Step 5 – Next, we shall look at the section describing the column data format. 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” allows us 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 keep the original column, we must mention a value here (the next adjacent cell).

Our result will be as follows:

Example #2

Split Name and 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 data format 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. 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.

This will open up the “Text to Columns” wizard.

Step 4 – In the next screen, we shall have to adjust the fixed-width vertical divider lines (called Break Lines) in the Data Preview section.

This can be adjusted as per user requirements.

Step 5 – Next, we shall look at the section describing the column data format. 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” allows us to choose the decimal separator and the thousands separator.

Next, we shall select the destination cell. If we do not modify this, it will overwrite the original column with “Name”; the adjacent cell will become “Sales”. If we keep the original column, we must mention a value here (the next adjacent cell).

Our result will be as follows:

We can also use the same logic to extract the first “n” characters from a data column.

Things to Remember about Separate Text in Excel

We should stop using complicated formulae, copy-paste to split a column (separate the clubbed data from a column), and start using Text to Columns.

Excel will split the data based on the character length in the Fixed-Width method.

In the Delimited method, Excel will split the data based on a set of delimiters such as commas, semicolons, tab,s, etc.

Easily access Text to Columns by using the Keyboard shortcut – Alt+A+E.