Concatenate Formula in Excel

The Concatenate function is a function that I use almost every day. There are numerous times where you will have data split into many columns but need to look up a value that is unique to the combination of two (or more) of those columns. That is where the concatenate formula comes in handy.

Let’s start by taking a look at the formula:

Concatenate(text1, text2, …)

text1, text2 can be either strings, numbers or cell references that you would like joined together. You can join as many strings into 1 string as you would like.

Example 1: =concatenate(“First”, “Last”) will return “FirstLast”
Example 2: =concatenate(“First,” “,”Middle”,” “,”Last) will return “First Middle Last”

Example 3: =Concatenate(B2,”, “,A2) will return “Smith, John”

Concatenate Shortcut
A quick substitute for the Concatenate formula is to use an Ampersand (& or Shift 7) Below are the same 3 examples as above except with the ampersand.

Example 1: =”First”&”Last” will return “FirstLast”
Example 2: =”First”&” “&”Middle”&” “&”Last will return “First Middle Last”
Example 3: =B2&”, “&A2 will return “Smith, John”

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

More Excel Tips

• Check if a Cell Contains Text
• Sum Values in a Range While Ignoring #N/A
• Summing Across Multiple Sheets
• Find the 2nd Largest Value in a Range
• Quick Way to Check for Duplicates in a Column