Concatenate Formula in Excel
- 0 Comments
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”
[ad#in-post-ad]
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.