Trending September 2023 # Text To Columns In Excel (Examples) # Suggested October 2023 # Top 17 Popular

You are reading the article Text To Columns In Excel (Examples) updated in September 2023 on the website Khongconthamnam.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 Text To Columns In Excel (Examples)

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

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?

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.

Solution:

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.

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

Step 4: Select “Space” from the delimiters.

Note: This example has spaces as delimiters.

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.

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.

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

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

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.

Step 1: Insert two columns after column A.

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.

Solution:

Step 1: Insert a column between Column A & Column B.

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.

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

Example #3 Delimited Option

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.

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

Step 3: Select “Delimited” from the “Original data type”, as shown below.

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

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.

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.