Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • Gmail
    • Productivity
      • Time Management
      • Task Automation
    Inside The Web
    Home»Excel»How to Add Text to Columns in Excel

    How to Add Text to Columns in Excel

    AsmiBy AsmiFebruary 7, 2023 Excel 5 Mins Read

    Although data entry is done best on a spreadsheet, you may sometimes receive data as text. It can get quite inconvenient to manually enter information in individual columns. To overcome this, Excel has a special feature called Text to Columns.

    Text to Column is a dedicated Excel feature to convert text into individual columns. Even though the application of this tool is quite simple, your approach may differ depending on your text. Similarly, this tool could be limited for more advanced conversion. In such cases, you can always head to the Power Query to split your text into columns.

    In this article, we’ve used four examples to help you understand how you can add text to columns in Excel using the Text to Column utility.

    Firstly, let’s discuss where you can find the Text to Columns tool.

    1. Open your Excel workbook.
    2. Head to the Data tab.
    3. Locate Text to Columns in the Data Tools section.
      Text to Columns

    Case 1: Text is Separated by Space

    Add text to column space

    Most text data are usually separated by space. You can use the space between your text as a separator to break text into columns. 

    In this example, we have our text separated by a space. You will have to set your separator as space in this case.

    1. Open Text to Columns.
    2. In Step 1 of 3, select the box next to Delimited, then select Next.
      Text to Wizard 1 of 3
    3. In Step 2 of 3, choose Space under Delimiters > Finish.
      Text to COlumn 2 of 3

    As a result, we have all four of our characters in four separate columns. If your text is separated by other characters such as commas, semicolons, or anything similar, simply enter the symbol in Step 2 of 3 of the Text to Column wizard.

    Case 2: Text that Follows a Format to Column

    Add text to column

    Let’s take for instance: you’re entering data from a form into individual columns. As forms usually follow a format, you can use that as an advantage to separate text into columns.

    For this example, we’re looking to separate “Name:Stacy Age:45 Occupation:Nurse” into three columns.

    1. Select your text in the grid and open Text to Columns.
    2. In Step 1 of 3, select Delimited > Next.
    3. In Step 2 of 3, choose Space > Finish.
      Text to Wizard 2 of 3 Excel
    4. Once you exit Text to Columns, use Ctrl + H to open Replace window.
    5. Next to Find what, enter *:
      Find and replace Excel
    6. Leave the Replace with section empty.
    7. Click the Replace All button.

    The Find and Replace tool in Excel supports wildcards such as the asterisk sign. What the asterisk does in this context is, it selects all data entered before the colon sign. As Name, Age, and Occupation preceded the colon sign, they were removed using the command.

    Case 3: Text is Not Separated but is in Camelcase

    Separate Text to Column Excel

    Another likely situation you may encounter is if your text comes without spaces between them. You will have to use Power Query to separate text as such into columns.

    Here, we’re looking to separate the name, AsmiDhakal. The text has no space in between, but the initials of the first and last names are in capital letters. You can also use this method to separate multiple texts into columns at the same time.

    1. Select your cell or range, then head to the Data tab.
    2. From the Get & Transform section, select From Table.
      Create new Query From Table 1
    3. In the Power Query editor, right-click on the column header.
    4. From the fly-out menu, head to Split Column > Lowercase to Uppercase.
      Split column by lowercase to uppercase excel
    5. Select Close and Load on the home tab.
      Close and Load Power Query Excel

    Case 4: Separate String Values

    Case 4

    For this occurrence, let’s assume you’re working with a report. The report is on the status of animal rehabilitation for five dogs. In your report, you need the Name, ID number, Status, and Date of Rehabilitation.

    Separating string values may come off as a bit tricky given that you’re dealing with both text and numeric values. However, Power Query has a dedicated feature to separate such values into columns.

    For this situation, we have divided one method into three steps for better retention.

    Step 1

    1. Select your data from the grid and head to the Data tab.
    2. In the Get & Transform section, select From Table.
      New Query From Table
    3. Right-click on the column, and select Split Column > Digit to Non-digit.
      Split column by digit to non digit excel

    In the first step, we used Digit to Non-digit to divide our row into four columns. The split column tool looked for a digit in our value and if a non-digit followed, the text was separated it into individual columns.

    Step 2

    Merge Columns 2
    1. Right-click on column 1.1.
    2. Select Split Column > Non-digit to Digit.
      Split column by non digit to digit
    3. Repeat this for column 1.2.

    Heading on to step 2, we further segregated columns 1.1 and 1.2 into four individual columns. We use Non-digit to Digit in this scenario to split “Daisy”, and “000123” in column 1.1, and “Adopted”, and “11”.

    Step 3

    Merge columns 1
    1. Click on column 1.2.2, hold Shift, and select column 1.3 and column 1.4.
    2. Right-click on any column header, and select Merge Columns.
      Merge column power query
    3. In the Merge Columns window, rename your column and click OK.
      Rename Merged column Excel
    4. Close and Load your data from the Home tab.
      Close and Load Power Query

    Now that we’re done splitting our text, let’s do some merging. If you’ve noticed, using Digit to Non-digit in step 1 separated “11/01/2023” which is our date. After Step 2, “11”, “/01”, and “/2023” are placed into individual columns. To place them in one column, we used the Merge Column tool with no separator selected.

    Cell Formats
    Asmi

      Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

      Add A Comment
      Table of Contents
      • Case 1: Text is Separated by Space
      • Case 2: Text that Follows a Format to Column
      • Case 3: Text is Not Separated but is in Camelcase
      • Case 4: Separate String Values
        • Step 1
        • Step 2
        • Step 3
      Recent Posts
      • How to Lock a Cell in Excel Formula
      • How to Make Gridlines Darker in Excel
      • How to Apply Cell Style in Excel
      • How to Apply Top and Bottom Border in Excel
      • How to Retrieve Last Value in Column Excel
      • Home
      • Privacy Policy
      © 2023 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.