Apr 30 2007

# 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

Did you find this post helpful? Try signing up for our free daily tips.

### 10 Comments on this post

1. Top Posts in 2008 | Excel Hints wrote:

[...] Concatenate Formula in Excel [...]

January 1st, 2009 at 4:53 pm
1. 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
2. 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
3. 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,
Jenna

July 13th, 2011 at 8:23 pm
4. 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
Dom

July 14th, 2011 at 12:07 pm
5. Sandy said:

Is it possible to use the concatenate with the alt+enter command?

November 4th, 2011 at 5:02 pm
6. 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
7. Dave said:

=concatenate(“First,” “,”Middle”,” “,”Last)

=concatenate(“First”,” “,”Middle”,” “,”Last”)

??

I am no excel expert, but your example looks maybe wrong?

August 22nd, 2012 at 10:45 am
8. 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
9. 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
Subscribe Form