EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips Excel Separate text
 

Excel Separate text

Manisha Sharma
Article byManisha Sharma
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 16, 2023

Separate text in Excel

 

 

Separate text in Excel (Table of Contents)
  • Introduction to Separate Text in Excel
  • What is Text to Columns?
  • Examples of Separate text in Excel

Introduction to Separate Text in Excel

To Separate Text in Excel, we can use the Text to Column option in the Data menu tab under Data Tools. We can also use this option with shortcut keys ALT + A + E simultaneously once we select the data we want to separate. Once we select the data and click on Text To Column, we would have two ways to separate. The first is Delimited, and the other is Fixed Width. Using Delimited, we can choose the criteria by which we want to separate a text, and with the help of Fixed Width, we can simply choose the width of the text from where we want to split it.

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 –

  1. Comma – “,”
  2. Semicolon – “;”
  3. Space – “”
  4. Tab – “
  5. Some other symbol

Separate text in Excel 1

We could also have all the data in a single column with a fixed number of characters marking the segregation in the data.

Separate text in Excel 2

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.

Separate text in Excel 3

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.

Example 1

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.

Separate text in Excel 4

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:

Separate text in Excel 5

Step 2 – We will navigate to the “Data” tab and then go to the “Data Tools” section and click on “Text to Columns”.

Separate text in Excel 3

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

Separate text in Excel 6

Step 3 – Now make sure that we click “Delimited” to select it and then click “Next”.

Separate text in Excel 7

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

Separate text in Excel 8

Then select “Semicolon” as the delimiter.

Separate text in Excel 9

When we select “Semicolon”, we see that the columns are demarcated in the text preview. When multiple successive delimiters exist, we can select the “Treat consecutive delimiters as one” option. Following that, we can click on the “Next” button.

Separate text in Excel 10

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)

Column data format 11

  • Ignore Column – This will skip reading the column.

Next, we shall look at the “Advanced” option.

Advanced option 12

“Advanced” allows us to choose the decimal separator and the thousands separator.

Separate text in Excel 13

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).

Separate text in Excel 14

After this, we shall click on “Finish”.

Finish Button 15

Our result will be as follows:

Separate text in Excel 16

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.

Example 2

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.

Separate text in Excel example 2.2

Step 2 – We will navigate to the “Data” tab and then go to the “Data Tools” section and click on “Text to Columns”.

Text to Columns in Excel 3

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

Separate text in Excel example 2.3

Step 3 – Now make sure that we click “Fixed width” to select it and then click “Next”.

Fixed width example 2.4

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.

Data Preview example 2.5

This can be adjusted as per user requirements.

Separate text in Excel example 2.6

We must 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 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)

Separate text in Excel 11

  • Ignore Column – This will skip reading the column.

Next, we shall look at the “Advanced” option.

Advanced option example 2.8.1

“Advanced” allows us to choose the decimal separator and the thousands separator.

Separate text in Excel 13

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).

Separate text in Excel example 2.8

After this, we shall click on “Finish”.

Click Finish example 2.9

Our result will be as follows:

Separate text in Excel example 2.10

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

  1. 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.
  2. Excel will split the data based on the character length in the Fixed-Width method.
  3. In the Delimited method, Excel will split the data based on a set of delimiters such as commas, semicolons, tab,s, etc.
  4. 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 a downloadable Excel template. You can also go through our other suggested articles –

  1. Excel Text with Formula
  2. Search For Text in Excel
  3. Formatting Text in Excel
  4. VBA Text

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Separate text Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Separate text Excel Template

EDUCBA

डाउनलोड Separate text Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW