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 Combine Text in Excel

    How to Combine Text in Excel

    AsmiBy AsmiMarch 9, 2023 Excel 4 Mins Read

    When entering data in spreadsheets, you may often find the need to combine text values such as names and addresses. For example, you could’ve received the first and last names of individuals, and your manager now wants you to create a list of their full names. 

    Manually jotting the information down in larger spreadsheets could take hours. However, Excel has different ways including using functions, operators, and built-in features for you to automate this, otherwise, tedious process.

    Using Flash Fill

    In our previous tutorial, we showed you how Flash Fill can be used to separate data from a single cell into different columns. But did you know that it can also do the opposite? That’s right – with Flash Fill, you can combine text from multiple columns into a single, unified cell.

    For this, all you have to do is create a pattern. This step is very important because you want Excel to understand that you’re trying to combine texts. To create the pattern, you need to have the text you want to combine in the same column.

    Autofill Excel

    In this example, we’re combining the first and last names of employees at ABC inc. into their full names.

    Notice how all first names are under column A and last names are under column B. In cell C2, I manually entered the full name. When I repeated this action in cell C3, Excel detected a pattern and automatically suggested filling in the remaining data. All I did was enter Tab and there were my data.

    Even if you don’t get the suggestion to fill your data, you can head to the Data tab and select Flash Fill. It should get the job done for you.

    Flash Fill Tool Excel

    Use the Ampersand Operator

    When dealing with bigger sets of data, I mostly use the Ampersand operator while combining texts in Excel. This is because operators are easier to work with compared to working with functions.

    Let’s use the same dataset we used before. To combine the text in cells A2 and B2, we used the Ampersand operator in the following formula:

    =A2&B2

    Use Ampersand to combine text Excel

    Merge Texts Using CONCAT and CONCATENATE

    If you like using functions, you can either use the CONCAT or the CONCATENATE functions. The formula used to join texts is very simple using these functions. The CONCAT function is available for Windows and Mac that are running Office 2019 or Office 365. If you’re using an older version, you can opt for the CONCATENATE function.

    If you’re looking to combine text values from cells A2 and B2, your formulas using the CONCAT and CONCATENATE functions should look something like this:

    =CONCAT(A2,B2)

    Use CONCAT to combine texts Excel

    =CONCATENATE(A2,B2)

    Use CONCATENATE to combine texts Excel

    Combine Texts Using a Delimiter in Between

    Although you can use delimiters in between other functions and the Ampersand function, the TEXTJOIN function has a dedicated argument to pass a delimiter. You could even choose to ignore or consider empty cells.

    In the above example, if few employees had middle names, the entire data could be thrown off. This way, excel can add a blank space when there is no middle name. But this isn’t convenient.

    Instead, you could use TEXTJOIN. This function ignores the adjacent empty cell instead of adding an extra space in between.

    Excel data range

    In this example, let’s combine the first, middle, and last names. If in case there is no middle name, we’ll be ignoring the empty cell. Check if your cell is truly empty, and enter the following formula in your spreadsheet:

    =TEXTJOIN(“ ”, TRUE, A2, B2, C2)

    TEXTJOIN function Excel

    Use Power Query to Merge Text Columns

    You could automate the process by using Power Query. This tool can be used to manipulate your data, such as splitting and merging.

    Although the name Power Query may be intimidating to new users, it is simpler to use than operators and functions. This is due to the lower likelihood of syntax errors.

    Try using Power Query instead of using formulas if you have the data you wish to merge in two or more columns.

    1. Select your data range and head to the Data tab.
    2. From the Get & Transform section, select From table.
      New Query from Table Excel
    3. In the Power Query editor, select the columns with the text you wish to combine. Select the first cell then hold Ctrl then click on the next column.
      Select Multiple Columns Excel Power Query
    4. Right-click on the column header and select Merge Columns.
      Merge Columns Power Query Excel
    5. If you want to add a delimiter in between, select one under Separator.
    6. You can also name your new column in the window.
    7. Click OK.
      Merge Columns
    8. From the Home tab, select Close and Load.
      Close and Load Power Query Excel
    Excel Basics
    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
      • Using Flash Fill
      • Use the Ampersand Operator
      • Merge Texts Using CONCAT and CONCATENATE
      • Combine Texts Using a Delimiter in Between
      • Use Power Query to Merge Text Columns
      Recent Posts
      • How to Calculate Discount Percentage in Excel
      • How to Create a Progress Bar in Excel
      • What is VSTACK in Excel
      • How to Separate Dates in Excel
      • How to Lock a Cell in Excel Formula
      • Home
      • Privacy Policy
      © 2023 Inside The Web

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