Concatenate Formula in Excel
- 7 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”
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
7 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
[...] Concatenate Formula in Excel [...]