How to use Text to Columns Option in Excel?

Text to Columns Option in Excel simplifies data manipulation by dividing text into separate columns based on specified delimiters. This feature enhances data organization and analysis, especially when dealing with large datasets or importing data from external sources. By utilizing Text to Columns, users can quickly parse and structure text-based data into a more manageable format without the need for complex formulas or manual adjustments. This functionality is invaluable for tasks such as splitting full names into first and last names, separating addresses into individual components, or extracting specific information from text strings. With its versatility and efficiency, the Text to Columns Option in Excel streamlines workflows and improves productivity for users across various industries and professions.

This Content Covers:

What is the Text to Columns Option in Excel and Why is it used?
How to Split Delimited Text using Text to Columns Option?
How to Convert Single Column Data into Multiple Columns With Text to Columns Option?
Converting Texts to Dates Using Text to Columns Option
Extract Any Amount of Characters from a List using the Text to Columns Option.

1. What is the Text to Columns Option in Excel and Why is it used?

Text to Columns is a popular Excel option that is used to divide the text into several columns depending on some delimited or any set width. In Excel, there are two ways to use the text in columns. One is to use a delimiter, such as a comma or a hyphen, which we input, or we can use a fixed identified width to divide the text into the adjacent columns.

2. How to Split Delimited Text using Text to Columns Option?

Here we have a list of names which includes the first and last names together in column A. The names are separated by some delimiters like coma (,), space, semicolon (;) etc. Now we will see how we can split the first and last names using Text to Columns option.

Step 1: Select the range and go to Data>>Text to Columns.

Step 2: When the Convert Text to Columns dialogue box is open, select Delimited and press Next.

Step 3: Select the delimiters that you have between the names. In this instance we had semicolon, comma, space and a “/” as delimiters. The “/” is not available under Delimiters section so I checked Other box and inserted this delimiter manually. So, if you have a delimiter in your data table that you can’t see under Delimiters option, you can manually insert it by selecting Other box. Keep the Treat consecutive delimiters as one box checked in case you have repetition of a delimiter. Press Next.

Step 4: Select a destination cell inside Destination box and press Finish.

Step 5: The names are split and presented in two different columns.

3. How to Convert Single Column Data into Multiple Columns With Text to Columns Option?

Here we have this dataset where the data is a bit complicated to understand. But if you look at the first cell of column A there is ID, Sales Rep, and Assigned To these three different information. Now we will convert these data from a single column into multiple columns by using the Fixed Width option from Text to Columns dialogue box.

Step 1: Select the range and press ALT+A+E keys one after another. This is a shortcut to open the Convert Text to Columns dialogue box. Select Fixed Width option and press Next.

Step 2: You will see the fixed-width divider vertical line marks also known as the break line inside the Data Preview window. You will need to drag and adjust them as per your data structure. Then press Next.

Step 3: Select a destination cell and select Finish option.

Step 4: Data from one column has been split among many columns. You can now view and comprehend them correctly.

4. Converting Texts to Dates Using Text to Columns Option

Here we have some dates in column A which are formatted as texts. When I clicked on a date its showing Text inside the format box inside Number section. Now we will convert these text dates into actual date format.

Step 1: Select the range and press ALT+A+E keys. Select Delimited option and press Next.

Step 2: Uncheck all the boxes>>Press Next.

Step 3: Select Date and choose a date format. Insert Destination and press Finish.

Step 4: The texts are now converted into actual dates.

5. Extract Any Amount of Characters from a List using Text to Columns Option.

Let’s say we wish to extract the numbers with three-digit decimals from column A.

Step 1: Press ALT+A+E after selecting the range. Select Fixed Width option and press Next.

Step 2: Drag the break line and position it accordingly. Press Next.

Step 3: Select the destination cell and select Finish.

Step 4: The numbers with 3-digit decimals are returned in column B. Now select the rest of the numbers from column C and press DELETE key to remove them.

Application of Text to Columns Option in Excel

Data Parsing: Text to Columns is used to split text data into separate columns based on specific delimiters such as commas, spaces, or tabs. This is particularly useful when dealing with data imported from other sources or formats.
Address Formatting: It helps in formatting addresses by splitting them into individual components such as street address, city, state, and zip code. This allows for better organization and analysis of address data.
Name Separation: Text to Columns can be used to separate full names into distinct columns for first name and last name. This is beneficial for databases and mailing lists that require individual name components.
Date and Time Separation: It can split combined date-time values into separate date and time columns, facilitating easier analysis and manipulation of temporal data.
Extracting Substrings: Users can extract specific substrings from text strings using Text to Columns, allowing for selective extraction of information based on predefined patterns or criteria.
URL Parsing: It helps in parsing URLs by dividing them into separate components such as protocol, domain, path, and parameters, enabling better analysis and categorization of web-related data.

For ready-to-use Dashboard Templates: