Excel Formula to Combine Text from Multiple Cells

Sharing is caring!

Do you need to combine text from multiple cells in Microsoft Excel? Merging the contents of different cells into a single cell is a common task that can be accomplished using a simple Excel formula. In this article, we’ll show you step-by-step how to use the CONCAT, CONCATENATE, &, and TEXTJOIN functions to easily combine text from multiple cells in Excel. Whether you’re a beginner or advanced Excel user, these powerful formulas will let you quickly join text together without any complicated steps.

Why Combine Text from Multiple Cells in Excel?

There are many reasons you may need to merge text from different cells into one in an Excel spreadsheet:

• Creating a full name by joining first and last names in separate cells
• Generating an address by combining house number, street, city, state and zip code cells
• Making an ID number by linking a prefix in one cell with a number in another cell
• Building a URL by merging a base domain in one cell with extensions in other cells
• Producing a sentence or paragraph by connecting bits of text from multiple cells

Rather than manually copying and pasting text together, which is time-consuming and error-prone, you can use a simple Excel concatenation formula to automatically combine content from various cells. This lets you join unlimited cells of text together instantly.

Using the & Operator to Combine Text

The simplest way to combine text from two cells is using the & operator. This lets you join the contents of cell A1 and cell B1 together like this:

=A1&B1

Whatever text is in cell A1 will be joined with the text in cell B1. For example, if A1 contains “John” and B1 contains “Smith”, the formula would output “JohnSmith”.

To add a space between the combined text, use “” with a space inside the quotes:

=A1&” “&B1

This will output “John Smith” with a space between the first and last names.

You can use the & operator to join text from as many cells as you want, like linking 5 cells together:

=A1&B1&C1&D1&E1

However, for joining lots of cells, the CONCAT, CONCATENATE and TEXTJOIN functions explained below are cleaner and easier.

Combining Text with the CONCAT Function

The CONCAT function makes it simple to combine text from multiple cells. Just specify the cells or text you want to join within the CONCAT parentheses:

=CONCAT(A1,B1)

To add a space or characters between the text, include it in quotes:

=CONCAT(A1,” “,B1)

You can combine as many cells as needed, and include spaces, symbols or static text:

=CONCAT(A1,”-“,B1,”:”,C1,”/”,D1)

If A1 is “Main”, B1 is “Floor 2”, C1 is “Suite 123”, and D1 is “Boston, MA”, the formula would produce:

“Main-Floor 2:Suite 123/Boston, MA”

So CONCAT is very versatile and powerful for joining unlimited cells of text together with spaces, characters and additional static text.

Merge Cells with the CONCATENATE Function

The CONCATENATE function works exactly like CONCAT to combine text from multiple cells:

=CONCATENATE(A1,” “,B1)

The only difference is that CONCATENATE was the original function, while CONCAT is a newer, improved version. But they both produce the same results, so you can use either one to join an unlimited number of text cells.

Join Cells with the TEXTJOIN Function

For even more advanced text combining, the TEXTJOIN function lets you specify a delimiter to separate the joined text and choose to ignore empty cells.

The syntax is:

=TEXTJOIN(delimiter,ignore_empty,text1,[text2],…)

For example:

=TEXTJOIN(” “,TRUE,A1,B1,C1,D1)

This joins the text from cells A1, B1, C1 and D1, separated by spaces (the delimiter surrounded by quotes), while ignoring any empty cells.

You can use any delimiter, like a comma, semicolon, slash, dash, colon, etc.:

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

This would combine A2, B2 and C2 with a comma and space between each.

The TEXTJOIN function is very handy when you have a long list of text cells to combine and some may be blank. The TRUE argument tells it to skip over empty cells rather than leaving gaps.

Combine Text & Numbers, Dates, Symbols

The concatenation formulas covered above can join any type of cell contents, not just text. Numbers, dates, times, currency and even symbols can all be combined.

For example, to generate a full date by linking cells with month, day and year numbers:

=CONCAT(A1,”/”,B1,”/”,C1)

If A1 is 12, B1 is 25 and C1 is 2023, the CONCAT formula would output:

12/25/2023

Concatenation automatically converts numbers and dates to text strings when combining them. However, number formatting like currency symbols and decimal places gets stripped out. If you need to preserve number formatting, use the TEXT function to convert the numbers to formatted text strings first.

Combine Horizontal Row or Vertical Column Cells

With all of the concatenation formulas, you can join cells horizontally in a row, vertically in a column, or in any other pattern.

The cells don’t need to be adjacent – you can skip columns or rows:

=CONCAT(A1,C1,E1)

=CONCAT(A1,A3,A5)

You’re free to arrange the cell references in any way to combine exactly the pieces of text you need.

Fixing Common Errors with Joining Text

The most common error when combining text from multiple cells is not including spaces or delimiters, causing words to run together. Be sure to add ” ” spaces or delimiters in quotes between each cell reference. For example:

=CONCAT(A1,” “,B1,” “,C1)

Another problem is #VALUE! errors due to trying to merge cells containing errors, formulas that return errors, or unrecognized/invalid text. Check that the cells you’re joining contain proper, valid text entries.

Sometimes formulas can return unexpected results if cells have extra spaces before/after text. Use the TRIM function on cells first to remove any leading/trailing spaces:

=CONCAT(TRIM(A1),” “,TRIM(B1))

Finally, ensure there are no typos in the function name or syntax errors with commas, parentheses, quotes, etc. Even small mistakes will cause a formula parse error.

Advanced Text Combining Techniques

For more advanced ways to combine text and data from multiple sources, check out these additional functions and techniques:

• Flash fill: Excel’s automatic data merging feature that detects patterns and populates combined text
• Power Query: Merge text from multiple Excel files, worksheets, CSV files and external data sources
• VLOOKUP & XLOOKUP: Look up and retrieve text from other locations to combine with a cell
• Nested concatenation: Combine the results of multiple concatenation formulas for complex merges

These advanced methods open up even more possibilities for automatically joining data in Excel.

Recap of How to Combine Text from Multiple Cells in Excel

To quickly recap, here are the best formulas to combine text from multiple cells in Excel:

• & Operator: =A1&” “&B1
• CONCAT Function: =CONCAT(A1,” “,B1)
• CONCATENATE Function: =CONCATENATE(A1,” “,B1)
• TEXTJOIN Function: =TEXTJOIN(” “,TRUE,A1,B1)

All of these let you combine text from as many cells as you want, in any arrangement. Include spaces, punctuation or other characters in quotes to separate the text. The TEXTJOIN function also lets you ignore empty cells.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.