EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Text to Columns in Excel

Text to Columns in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated July 3, 2023

Text to Columns in Excel

Introduction

Text To Column option in Excel is available in the Data menu tab under the Data Tools section, which is used for separating text available in a cell or column to the columns by splitting them with different criteria.

Start Your Free Excel Course

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

Text to columns in Excel is used to split the contents of a cell into two or more columns using the fixed-width or delimiter method.

For instance, if you have a list of fruits and their prices written together in a single cell like “Banana ($0.85), Orange ($1.50), Apple ($1.75)“, then you can use the “Text to Columns” tool to split this data into separate columns for each fruit and its price.

Where is Text to Columns in Excel?

The “Text to Columns” option is present in the “Data tools” of the “Data” tab”.

Where is Text to Columns in Excel

There are two options for separation in Text to Column in Excel:
You can use delimiter or fixed-width options depending on the data and how you want to split the text.

Options

Description Example

Pros

Delimited Splits text based on a specific character, such as commas, tabs, spaces, semicolons, hyphens, etc. If you have a cell with “Olivia Alex,456 Main Street, Austin, USA”, you can split it into name, address, and location. This method is quick and easy to use.
Fixed Width Splits text based on a specific character count, dividing the values into specific columns. If you have a 9-lettered name and want to split it at the 6th character. This method is useful when dividing text with a specific character count.

How to Use Text to Columns in Excel?

Text to Columns in Excel is simple and easy to use. With some examples, let’s understand how to convert Text to Columns in Excel.

You can download this Text to Columns in Excel Template here – Text to Columns in Excel Template

Example #1 Delimited Option

Purpose: Split the given names into First Name and Last Name.

Consider the data below, which contains a few individuals’ phone numbers. Here, First Names and Last Names are present in a single column. We want to split each name into First and Last Names using the delimited option.

Example #1 Delimited Option

Follow the below steps to accomplish this task.
Solution:

Step 1: Insert a column between columns A & B. To insert another column, right-click on column B, click insert, or use the shortcut “Ctrl +”.

Example 1 step 1

Note: If you do not insert another column after column A, then the other portion of data will overwrite in the adjacent column. For example, the first name will appear in Column A, and the last name will appear in Column B by replacing the phone number.

Step 2: Select the data set of column A. Go to the “Data” Tab and click on the “Text to Column” option in the “Data Tools” section, as shown below.

Example 1 step 2

A “Convert Text to Column Wizard” dialog box will appear. The Wizard has three steps.
Step 3: Select the “Delimited” option from the Original Data Type. Click on “Next,” as shown below.

Note: In our case, a name is separated by space. So, we have to select the Delimited option.

Example 1 step 3

Step 4: Select “Space” from the delimiters.

Note: This example has spaces as delimiters.

Example 1 step 4

Note: A “Data preview” window at the bottom of the “Convert Text to Columns Wizard” dialogue box will display the result.  We can see how the delimiters will affect the data or, in other words, how the result will look.

Step 5: Select “General” in the “Column data format” section. Select the destination cell and then click on “Finish”.

Note: If you don’t select a destination cell, it will overwrite your existing data set in the first column with the first name and the last name in the adjacent column. Choose a different destination cell to keep the original data intact.

Example 1 step 5

The result is displayed below. Here, the first name appears in column A, and the last name appears in column B.

Example 1 step 5-2

Step 6: Make the column name more specific, as shown below.

Example 1 step 6

Let’s take another scenario where you want to keep the original data intact and want to display First Name in Column B and Last Name in Column C.

Follow the below steps:
Step 1: Insert two columns after column A.

Example 1 scenario 2 Step 1

Step 2: Follow steps 2 to steps 4 from the above example.
Step 3: In Step 3 of the Wizard, select the “General” and “Destination” cells where we want to display the result, as shown below. We want our result to appear in Column B, so click on Cell B2, and the wizard will automatically take the destination.
Step 4: Click on “Finish”.

Text to Columns in Excel-Example 1 scenario 2 Step 4

The result is displayed below. Here, the original data of column A is intact, the first name appears in Column B, and the last name appears in Column C.

Example 1 scenario 2 Step 4-2

Example #2 Fixed-width Method

Purpose: Split date and time.

The below data set contains details of the students who filled out a Google form. Column A includes data in date and time format, like 4/1/2023 at 9:58:34 AM. Here, we want to separate the date and time into columns, i.e. 4/1/2023 in one column and  9:58:34 AM in another.

Example 2

Solution:

Following are the steps for separating date and time using the fixed-width method.
Step 1: Insert a column between Column A & Column B.

Text to Columns in Excel-Example 2 Step 1

Step 2: Select the data set of column A and Go to the “Data” tab. Click “Text to Column” under the “Data Tools” Tab.

Example 2 Step 2

A dialog box will appear after clicking on Text to Columns.
Step 3: Select “Fixed width” and click “Next”, as shown below.

Example 2 Step 3

Step 4: Create a break line in the desired position. Here, we have created a line between date and time.

Note: The instruction for column break is already given in the dialog box, like how to create, delete and move a broken line. The column break allows us to set the field width (character count) as how we want to separate the data. We can divide it into two columns, i.e. Date format in one column and time format (AM/PM) in another column format, or we can have a date in one column, time in another, and AM-PM in another one. We can also see how the data will look in the preview section.

Text to Columns in Excel-Example 2 Step 4

Step 5: Click on “Next”.
Step 6: Select the “General” and “Destination” cells, as shown below.
Step 7: Click on “Finish”.

Example 2 Step 7-1

The result is displayed below. Here, the time is separated from the date.

Example 2 Step 7-2

Example #3 Delimited Option

Purpose: Convert Single Column Data into Multiple Columns by Space and Hyphen.

Consider the below example. Column B contains audit details along with the employee’s name and ID. Here, all data is in one single column. We want to display the employees’ IDs, first names, and last names in three columns. A hyphen and space separate the data in Column B, so we will use the delimited option to split it.

Example 3

Solution:
Step 1: Insert three columns between column B and Column C

Text to Columns in Excel-Example 3 Step 1

Step 2:  Select the data of column B and click on “Text to Columns” under the Data tab.
A “Convert Text to Columns Wizard” dialog box will open.
Step 3: Select “Delimited” from the “Original data type”, as shown below.

 Text to Columns in Excel-Example 3 Step 3

Step 4: Select “Space” from the delimiters. Also, select “Other” and enter a hyphen (-) in the box right to the other option. The data preview section will display how the data will look.
Step 5: Click on Next.

Example 3 Step 5

Step 6: Select “General” from the “Column data format” and the “Destination” cell.

Text to Columns in Excel-Example 3 Step 6

Step 7: Click on Finish.
The result is displayed below. Here, the original data of column B is split into three columns, and we have named it ID, first name, and second name, respectively.

Example 3 Step 7

Things to Remember

  • The keyboard shortcut to access Text to Columns in Excel is Alt + A + E.
  • Use the delimited method when the text strings contain commas, spaces, semicolons, and other special characters.
  • Identify the correct delimiter while using the delimited feature. For instance, in “Karen, Wilson”, the delimiter is a comma (,).
  • Use the fixed-width method to separate text using character count.
  • A Data Preview window is at the bottom of the Text to Columns wizard dialog box. After choosing the format options, the data preview will display how your data will look.
  • Always insert columns equal to the number of data you want to separate.
  • In fixed width, create an arrow in the desired position to split the data.
  • The final result of the Text to Columns in Excel is static. You must repeat the process to get updated results if there are any changes in the original data.
  • Select the proper destination cell where you want the result. Otherwise, it can lead to overwriting of data in the existing column.
  • If you want the original data, make a copy or select another destination cell.

Assignment for You: 2-Minute Challenge!

You have got 2 minutes to put your skills to the test and see if you can solve this problem on your own. Don’t worry. You’ve got this! You are already familiar with all the steps, so let’s see how quickly you can complete this assignment.

To complete the assignment, please download this template.

You can download this Assignment Template here – Assignment Template

In the template, we have provided a list of usernames, followers, and engagement rates for an influencer campaign. Your task is to separate this information into different columns using the “Text to Columns” tool in 2 minutes.

If you would like more assignments like this, feel free to let us know in the comments.🙂

Recommended Articles

This article has been a guide to Text to Columns in Excel. Here we discuss its uses and how to convert Text to Columns in Excel with some examples and downloadable Excel templates. You may also look at these useful functions in Excel –

  1. VLOOKUP Function in EXCEL
  2. LOOKUP in Excel
  3. Excel Row Count
  4. Excel Solver Tool
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

Download Text to Columns in Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

EDUCBA

Download Assignment Template

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Text to Columns in Excel Template

EDUCBA

डाउनलोड Text to Columns in Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more