Learn how to separate text in excel in this article. There are many ways to do it, so know about it.
While you work on Excel, sometimes you create files and sometimes you receive files that are created by others. Many times you would see that the data added is not properly formatted.
Sometimes all values would be in the same cell. It would be really a waste of time to separate each value manually hence Excel gives us an option to do so quickly and here is how you can do it.
Table of Contents
How To Separate Text In Excel
How To Split First Name, Last Name, and Age into separate text columns in excel (using delimiters)
Let us suppose that we have received the data in the following format where we have “First Name”, “Last Name”, and “Age” data all into one column.
We have to split the data into separate text columns in excel.
For this, we need to follow the following steps:
1. We will first select the data column:
2. Navigate to the “Data” tab and then go to the “Data Tools” section and click on “Text to Columns”.
This will open up the “Text to Columns” wizard.
3. Click on “Delimited” to select it and then click on “Next”.
4. In the next tab, deselect “Tab” first.
Select “Semicolon” as the delimiter.
Now we will see that the columns are demarcated in the text preview. In the situation where there are multiple successive delimiters, check the “Treat consecutive delimiters as one” option. Click on the “Next” button.
5. In 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.
Let’s look at the “Advanced” option.
Here you can choose the decimal separator and the thousands separator.
Select the destination cell. You must modify this, or 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”.
In case you want to keep the original column, we need to mention a value here (which will be the next adjacent cell).
Now click on “Finish”.
The result will be as follows:
Also Read
How to wrap text in Excel
How to insert Tick Mark in Excel
How To Split Name, Sales into separate text columns in excel (using Fixed Width)
We have data, as shown below.
The first column consists of “Name” and the next column is “Sales”. “Name” has a length of 4, and “Sales” has a length of 5. In such cases, 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 – Go to the “Data” tab > “Data Tools” and click on “Text to Columns”.
This will open up the “Text to Columns” wizard.
Step 3 – Click on “Fixed width” to select it and then click on “Next”.
Step 4 – Adjust the fixed-width vertical divider lines in the Data Preview section.
Also Read
How to Protect cells in Excel
How to split cells in Excel
This can be adjusted as per user requirements.
Click on the exact point where the first column width ends to bring the Break Line to that point.
Step 5 – 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.
Go “Advanced” option.
Choose the decimal separator and the thousands separator.
Next, we shall select the destination cell.
Modify this else it will overwrite the original column with “Name”; the adjacent cell will become “Sales”. In case we keep the original column, we will need to mention a value here.
Click “Finish”.
The sheet will now look like this:
conclusion
Microsoft Excel is one of the oldest spreadsheet software programs, and it has gone through several upgrades and fixes. This powerful spreadsheet program was first released in 1985 for Macintosh-based systems. It was later released for other popular platforms, including Windows.
MS Excel allows us to perform a wide range of tasks, from simple ones like calculating the average to more complex ones like VLOOKUP function.
Easysheet is a blog that features incredible Excel hacks, tips, and tricks. Our goal is to help beginners learn Excel by using EasySheet and get answers to all of their questions.
We are the best place to go for anyone who needs assistance with Microsoft Office applications such as Excel, Word, PowerPoint, Publisher, and others.
