Concatenate Formula in Excel
 10 Comment
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#inpostad]
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
10 Comments on this post
Trackbacks

Diane said:
Is it possible to combine the concatenate and if functions?
I want to concatenate cells (D21:BD21) if (D21:BD21) equals 1.
June 15th, 2011 at 1:02 pm 
john said:
Yes it probably is. Do you want to say if any of those cells equals 1 or if the sum of those cells equal 1 or something else?
June 16th, 2011 at 1:20 pm 
Jenna said:
I am trying to create a spreadsheet that clients can use easily.
What I’m trying to do i on one sheet they type in a specific set of numbers in a cell and it will pull only their information.
For example, if i type in the last five of their social in a cell, it will pull up the date i received their information, and what number they are in line (3 of 39) in other cells.
Any ideas would be appreciated.
Thanks,
JennaJuly 13th, 2011 at 8:23 pm 
Dominique said:
Hello,
Is it possible to concatenate a mixed between text and functions…
=CONCATENATE(“Notify on all alerts raised by any instance in a “, “=HYPERLINK(‘#Groups!A2′,’Groups’)”, ” group”)Thanks
DomJuly 14th, 2011 at 12:07 pm 
Sandy said:
Is it possible to use the concatenate with the alt+enter command?
November 4th, 2011 at 5:02 pm 
Bob S. said:
Is it possible to create an equation from this command?
I have four cells, I called the first cell ‘=’
Second is the worksheet number in a long series of worksheets designated with a consecutive number.
Third is a cell in the worksheet.When I concatenated, I ended up with this:
=’TP04′!B5
It SHOULD be a formula that returns the value in B5 from worksheet TP04, but it just shows it as text.
November 7th, 2011 at 3:54 pm 
Dave said:
Your example 2:
=concatenate(“First,” “,”Middle”,” “,”Last)
Should that not read
=concatenate(“First”,” “,”Middle”,” “,”Last”)
??
I am no excel expert, but your example looks maybe wrong?
August 22nd, 2012 at 10:45 am 
Bill said:
In addition to the above correction (August 22nd, 2012), your Concatenate Shortcut example 2:
Example 2: =”First”&” “&”Middle”&” “&”Last will return “First Middle Last”
should have a closing quote after Last
Example 2: =”First”&” “&”Middle”&” “&”Last” will return “First Middle Last”
September 10th, 2012 at 1:43 pm 
KB said:
Concatenate: the most USELESS function in excel.
As already stated in the article the exact same functionality is given by the & sign which saves you from typing “CONCATENATE” with the same result…Also it cannot be used with ranges, but only cell by cell.
In short: never use it, it is a duplicate for the shorter use of “&”
January 8th, 2013 at 10:53 am
[...] Concatenate Formula in Excel [...]