Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
- Power Bi (35+)
Text to Columns in Excel is a method which is used to separate a text in different columns based on some delimited or any fixed width, there are two options to use text to columns in excel one is by using a delimiter where we provide delimiter as an input such as comma space or hyphen or we can use fixed defined width to separate a text in the adjacent columns.
Table of Contents
Convert Text to Columns in Excel
Let’s understand how to convert text to columns in excel with examples.
As the name itself suggests, it is used to split the text into multiple columns in Excel. For example, if you have a first name and last name in the same cell, you can use this to quickly split these into two different cells.
Excel Text to Columns or Split Cells in Excel is used in data cleaning, data re-arrangement, data manipulation. It can be difficult to work with a lot of text in a single column.
There are so many complex formulas in excel that can help you in splitting your huge text into adjacent columns; it is so complex and confusing.
It’s Excel’s great tool for splitting up text into more manageable columns. Moreover, it is much easier than trying to work with formulas to do the same thing!
This can be very handy when you get your data from databases or you import it from other file formats such as Text or CSV.
In this article, I will explain the useful things that can be done by using TEXT to COLUMNS in excel.
Where to find Text to Columns in Excel?
In order to access Text to Columns in Excel, go to Data then Data Tools and Text to Columns.
To open Text to Columns, the keyboard shortcut is – ALT + A + E.
Examples #1: Split First Name and Last Name
Assume you have the list of names that includes both First Name & Last Name in a single column.
Now you need to split the first name and last name separately.
Now, we need to split the first name and the last name and get these in separate cells, follow the below steps:
Step 1: Select the data
Step 2: Press ALT + A +E this will open the Convert Excel Text to Columns Wizard.
Step 3: Now, make sure Delimited is selected and click on Next.
Step 4: In the next step first uncheck TAB and select SPACE as the delimiter. If you think that, there are double/triple consecutive spaces between the names, then select ‘Treat consecutive delimiters as one’ option. Click on Next.
Step 5: In Step 5, select the destination cell. If you do not select a destination cell, it would overwrite your existing data set with the first name in the first column and last name in the adjacent column. If you want to keep the original data intact, either create a copy or choose a different destination cell.
Step 6: Click on FINISH. That will split the first name and last name separately.
Note: This technique is ideal only for first name and last name. If there are initials and middle name you need to use the different technique.
Examples 2: Convert Single Column Data into Multiple Columns
Let us see how to split the data into multiple columns. This is also part of data cleaning. Sometimes your data are in one single column, and you need to divide it into multiple adjacent columns.
Below data is in one column and you need to convert it into 4 columns based on the heading.
From the above data, we can understand that there are four pieces of information in a single cell i.e. Hex No., Description. Hex No., Description. Now we will apply the “Fixed width” method of Text to Columns.
Step 1: Select the data range.
Step 2: Go to Data and select Text to Column excel option (ALT + A + E), this would open up the Text to Column Wizard window.
Step 3: Select Fixed Width option and click on Next.
Step 4: You will see the fixed width divider vertical line marks (called Break line) in the Data Preview window. You may need to adjust it as per your data structure.
Step 5: Click on next option and in the next option select the destination cell as B1. This would insert the data in the new column so that we will have our original data as it is.
Step 6: Now click on Finish button and this would instantly split data into 4 columns i.e. Hex, Description, Hex, and Description starting from column B to column E.
Examples 3: Convert Date to Text Using Text to Column Option
If you do not like formulas to convert the date to text format, you can use TEXT TO COLUMN Excel OPTION. Assume you have data from cells A2 to A8.
Now you need to convert it into text format.
Step 1: Select the entire column you want to convert.
Step 2: Go to Data and Text to Columns
Step 3: Make sure delimited is selected and click on next button.
Step 4: Now, the below pop up will open and uncheck all the boxes and click the Next button.
Step 5: Select the TEXT option from the next dialogue box. Mention the destination cell as B2 and click finish.
Step 6: Now it instantly converts it into text format.
Examples 4: Extract First 9 Characters from the list
For example, in the data set shown below, the first 9 characters are unique to a product line.
Step 1: Select the data range.
Step 2: Press ALT + A + E and select the Fixed width and click on Next.
Step 3: Now put a delimiter exactly after the 9th character as I have shown in the below image.
Step 4: Click on next and select the destination cell as B2
Step 5: Click on Finish and this will extract the first 9 characters from the list in column B and remaining characters in column C.
This has been a guide to Text to Columns in Excel. Here we discuss where to Convert Text to Columns Excel along with the example and downloadable excel templates. You may also look at these useful functions in excel –
- Freeze Columns in Excel
- Text in Excel Formula
- Convert Rows to Columns in Excel
- Excel Split Panes
- Excel Hiding Formula
- Detele Blank Rows in Excel
- Methods to Insert Multiple Rows in Excel
- Insert Hyperlinks Excel
- Convert Text to Number in Excel
- How to Unhide Columns in Excel?
- Date to Text in Excel
- Numbers to Text in Excel
- TEXT Function Excel