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”

[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.

More Excel Tips

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

Enter your email address: 

          

10 Comments on this post

Trackbacks

  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:

    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
  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

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives