Text to Columns in Excel

The Text to Columns in Excel is a feature that distributes the texts in different cells using spaces, commas, words, etc. The feature adjusts the width of the cell according to the text length. The Text to Columns in Excel is in the Data Tools group of the Data tab.

For example, the succeeding image shows the text in cell A2, and we will split the contents between cells B2 and C2 using the Text to Columns in Excel.

The result is shown in cell B2 as ‘Apple’ and cell C2 as ‘Banana.’

Similarly, using Text to columns in excel, we can find the desired output.

Table of contents

• What is Text to Columns in Excel?
  • Where to Find the Text to Columns Option in Excel?
  • How to Use Text to Columns in Excel?
    • #1 – Split First Name and Last Name
    • #2 – Convert Single Column Data into Multiple Columns
    • #3 – Convert Date to Text using the Text to Column Option
    • #4 – Extract the First 9 Characters from the List

    Key Takeaways

    • There are two methods used the Fixed Width Method and the Delimited Method.
    • Set the delimiter in the text selected for splitting into columns. For example, in “John, David,” the comma (,) is a delimiter.
    • Always Insert several columns equal to the number of texts in the cell. For Example, if a cell value is Apple – Banana – Cranberry, then the total column needed is 3; the data has one, and it needs to be separate, so insert two more columns.

    Where To Find Text to Columns Option In Excel?

    We can insert Text to Columns in Excel using the following steps:

    

    1. Select the cell which will contain the result.
    2. Go to the Data tab.
    3. Click on the Text to Columns option from the Data Tools group.

    Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

    If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

    How To Use Text To Columns In Excel?

    #1 – Split First Name And Last Name

    For example, the succeeding image shows the full name in column A, and we will split the contents between cells B2 and C2 using the Text to Columns in Excel.

    

    The steps to Splitting the first name and last name are as follows:

    • Step 1: Select the cells which we want to split. In this case, the selected cell is cell A2.
    • Step 2: Go to the Data tab.
    • Step 3: Select the Text to Columns option from the Data Tools group.
    
    • Step 4: The window Convert Text to Columns Wizard – Step 1 of 3 opens up.
    • Step 5: Select the Delimited radio button from the Original data type list.
    • Step 6: Click Next.
    
    • Step 7: The window Convert Text to Columns Wizard – Step 2 of 3 opens up.
    • Step 8: Select the Space checkbox from the Delimiters list.
    • Step 9: Click Next.
    
    • Step 10: The window Convert Text to Columns Wizard – Step 3 of 3 opens up.
    • Step 11: Select the General radio button from the Column data format list.
    • Step 12: Set the cell reference in Excel where we want to print the result in the Destination box. In this case, the cell reference will be $B$2.
    • Step 13: Click Finish.
    
    • Step 14: The result is displayed in cells B2 and C2, as the full name Jenifer Lorence is splatted in the first name as Jenifer and the last name as Lorence.
    

    #2 – Convert Single Column Data Into Multiple Columns

    For example, the succeeding image shows an idiom as a string in column A, and we will split the Single data between multiple columns using the Text to Columns in Excel.

    

    The steps to Splitting the Single data between multiple columns are as follows:

    • Step 1: Select the cells which you want to split. In this case, the selected cell is cell A2.
    • Step 2: Go to the Data tab.
    • Step 3: Select the Text to Columns option from the Data Tools group.
    
    • Step 4: The window Convert Text to Columns Wizard – Step 1 of 3 opens.
    • Step 5: Select the Delimited radio button from the Original data type list.
    • Step 6: Click Next.
    
    • Step 7: The window Convert Text to Columns Wizard – Step 2 of 3 opens.
    • Step 8: Select the Space checkbox from the Delimiters list.
    • Step 9: Click Next.
    
    • Step 10: The window Convert Text to Columns Wizard – Step 3 of 3 opens up.
    • Step 11: Select the General radio button from the Column data format list.
    • Step 12: Set the cell reference where we want to print the result in the Destination box. In this case, the cell reference will be $B$2.
    • Step 13: Click Finish.
    
    • Step 14: The result is displayed in the cells B2, C2, D2, E2, F2, and G2, as the string “Time and Tide wait for none.” is splatted in as “Time,” “and” “Tide,” “wait,” “for” and “none.”
    

    #3 – Convert Date To Text Using Text To Column Option

    For example, the succeeding image shows the date in column A, and we will convert the date to text using the Text to Columns in Excel.

    

    The steps to convert text to columns in excel are as follows:

    • Step 1: Select the cells which we want to split. In this case, the selected cell is cell A2.
    • Step 2: Go to the Data tab.
    • Step 3: Select the Text to Columns option from the Data Tools group.
    • Step 4: The window Convert Text to Columns Wizard – Step 1 of 3 appears.
    • Step 5: Select the Delimited radio button from the Original data type list.
    • Step 6: Click Next.
    
    • Step 7: The window Convert Text to Columns Wizard – Step 2 of 3 appears.
    • Step 8: Uncheck all the checkboxes from the Delimiters list.
    • Step 9: Click Next.
    
    • Step 10: The window Convert Text to Columns Wizard – Step 3 of 3 opens up.
    • Step 11: Select the Text radio button from the Column data format list.
    • Step 12: Set the cell reference where we want to print the result in the Destination box. In this case, the cell reference will be $B$2.
    • Step 13: Click Finish.
    
    • Step 14: The result is displayed in cell B2, as the text ‘23-06-2022′.
    

    Likewise, using Text to columns in Excel, we can convert text in Excel.

    #4 – Extract First 9 Characters From The List

    For example, the succeeding image shows a string in column A, and we will extract the first nine characters from the list using the Text to Columns in Excel.

    

    The steps to extract the first nine characters from the list are as follows:

    • Step 1: Select the cells which we want to split. In this case, the selected cell is cell A2.
    • Step 2: Go to the Data tab.
    • Step 3: Select the Text to Columns option from the Data Tools group.
    
    • Step 4: The window Convert Text to Columns Wizard – Step 1 of 3 opens up.
    • Step 5: Select the Fixed width radio button from the Original data type list.
    • Step 6: Click Next.
    
    • Step 7: The window Convert Text to Columns Wizard – Step 2 of 3 opens up.
    • Step 8: Put the Delimiter arrow exactly after the nine characters. In this case, the arrow is set after ‘C’ of ‘Columns.’
    • Step 9: Click Next.
    
    • Step 10: The window Convert Text to Columns Wizard – Step 3 of 3 opens.
    • Step 11: Select the General radio button from the Column data format list.
    • Step 12: Set the cell reference where we want to print the result in the Destination box. In this case, the cell reference will be $B$2.
    • Step 13: Click Finish.
    
    • Step 14: The result displayed in cell B2 is shown as Text to C and splatted in cell C2 showings columns, cell D2 showings in, and cell E2 showing Excel.
    

    Text To Column Shortcut

    The shortcut keys to Text to Columns in Excel are;

    Select the cells which we want to split.

    

    ALT key + A for Data tab + E for Split Text to Columns

    Important Things To Note

    • The Text to Columns in Excel splits a text into different columns based on delimited or fixed width.
    • The CONCATENATE Excel function is used to concatenate the texts.
    • The Text to Columns can split email ids also.
    • The Text to Columns in Excel can convert the date formats.
    • The Text to Columns in Excel can convert numbers with trailing minus signs to negative numbers.
    • If the cell address entered is wrong, the error is generated.
    • Text can be separated with tabs, semicolons, space, commas, or any other.

    Frequently Asked Questions (FAQs)

    1. What does Text to Columns do in Excel?

    We can separate Text to Columns in Excel into different columns based on delimited or fixed width. There are two ways to split Text into Columns in Excel. The first uses a delimiter in which we provide input as comma, space, or hyphen; the second is a fixed defined width to separate a text in the adjacent columns.

    2. How to remove Text to Columns in Excel?

    The Text to Column feature in Excel is removed by CONCATENATE function present in the More Functions in the Compatibility option in the Function Library of Formula tab. It merges the selected cells into one cell.

    For example, the succeeding image shows the first name in column A and the last word in column B, and we will remove Text to Columns in Excel using CONCATENATE function. The result is shown in cell C2 as “James Potter.”

    3. How to move Text from Columns to Rows in Excel?

    The TRANSPOSE function can move the Text from Columns to rows in Excel. It transposes the column’s data into the row and vice versa.

    For example, the following image shows the array of three numbers “10 and 20” as input in Row 2. We must transpose the data table into the column with the TRANSPOSE formula.

    • Select the empty cell “D2:D3”.
    • Type the formula =TRANSPOSE(Select the array “A2:B2”).
    • Press CTRL + SHIFT + ENTER.

    The Output comes as 10 and 20 in the column as a vertical representation.

    Download Template

    This article must help understand Text to Columns in Excel with its formula and examples. You can download the template here to use it instantly.

    Recommended Articles

    This has been a guide to Text to Columns in Excel. Here we learn how to use text to columns option in Excel with examples and downloadable excel template. You can learn more from the following articles –

    • ISTEXT Excel Function
    • Column Sort In Excel
    • Convert Text To Numbers In Excel