CONCATENATE Excel Range (with and without separator)

In Excel, there are two ways to combine the contents of multiple cells:

  • Excel CONCATENATE function (or the ampersand (&) operator)
  • Excel TEXTJOIN function (new function in Excel if you have Office 365)
If you’re using Excel with Office 365 subscription, I suggest you click here to skip to the part where the TEXTJOIN function is covered.

If you’re not using Office 365, keep reading.

In its basic form, CONCATENATE function can join 2 or more characters of strings.

For example:

  • =CONCATENATE(“Good”,”Morning”) will give you the result as GoodMorning
  • =CONCATENATE(“Good”,” “, “Morning”) will give you the result as Good Morning
  • =CONCATENATE(A1&A2) will give you the result as GoodMorning (where A1 has the text ‘Good’ in it and A2 has the text ‘Morning’.

While you can enter the reference one by one within the CONCATENATE function, it would not work if you enter the reference of multiple cells at once (as shown below):Concatenate Excel Range using function

For example, in the example above, while the formula used is =CONCATENATE(A1:A5), the result only shows ‘Today’ and doesn’t combine all the cells.

In this tutorial, I will show you how to combine multiple cells by using the CONCATENATE function.

Note: If you’re using Excel 2016, you can use the TEXTJOIN function that is built to combine multiple cells using a delimiter.

CONCATENATE Excel Range (Without any Separator)

Concatenate Excel Range Without separator

Here are the steps to concatenate an Excel range without any separator (as shown in the pic):

  1. Select the cell where you need the result.
  2. Go to formula bar and enter =TRANSPOSE(A1:A5)
    • Based on your regional settings, you can also try =A1:A5 (instead of =TRANSPOSE(A1:A5))
  3. Select the entire formula and press F9 (this converts the formula into values).
  4. Remove the curly brackets from both ends.
  5. Add =CONCATENATE( to the beginning of the text and end it with a round bracket).
  6. Press Enter.

Doing this would combine the range of cells into one cell (as shown in the image above). Note that since we use any delimiter (such as comma or space), all the words are joined without any separator.

CONCATENATE Excel Ranges (With a Separator)

Concatenate Excel Range with a separator (space)

Here are the steps to concatenate an Excel Range with space as the separator (as shown in the pic):

  1. Select the cell where you need the result.
  2. Go to formula bar and enter =TRANSPOSE(A1:A5)&” “
    • Based on your regional settings, you can also try =A1:A5 (instead of =TRANSPOSE(A1:A5)).
  3. Select the entire formula and press F9 (this converts the formula into values).
  4. Remove the curly brackets from both ends.
  5. Add =CONCATENATE( to the beginning of the text and end it with a round bracket).
  6. Press Enter

Note that in this case, I used a space character as the separator (delimiter). If you want, you can use other separators such as a comma or hyphen.

CONCATENATE Excel Ranges (Using VBA)

Below is an example of the custom function I created using VBA (I named it CONCATENATEMULTIPLE) that will allow you to combine multiple cells as well as specify a separator/delimiter.

Excel Concatenate Ranges - custom function

Here is the VBA code that will create this custom function to combine multiple cells:

Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Ref
 Result = Result & Cell.Value & Separator
Next Cell
CONCATENATEMULTIPLE = Left(Result, Len(Result) - 1)
End Function

Here are the steps to copy this code in Excel:

  • Go to the Developer Tab and click on the Visual Basic icon (or use the keyboard shortcut Alt + F11).Excel Concatenate Ranges multiple cells - Visual Basic
  • In the VB Editor, right-click on any of the objects and go to Insert and select Module.Inserting a module to create function to combine multiple cells into a single cell
  • Copy paste the above code in the module code window.
  • Close the VB Editor.

Click here to download the example file.

Now you can use this function as any regular worksheet function in Excel.

CONCATENATE Excel Ranges Using TEXTJOIN Function (available in Excel with Office 365 subscription)

In Excel that comes with Office 365, a new function – TEXTJOIN – was introduced.

This function, as the name suggests, can combine the text from multiple cells into one single cell. It also allows you to specify a delimiter.

Here is the syntax of the function:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

  • delimiter – this is where you can specify a delimiter (separator of the text). You can manually enter this or use a cell reference that has a delimiter.
  • ignore_empty – if this is TRUE, it will ignore empty cells.
  • text1 – this is the text that needs to be joined. It could be a text string, or array of strings, such as a range of cells.
  • [text2] – this is an optional argument where you can specify up to 252 arguments that could be text strings or cell ranges.

Here is an example of how the TEXTJOIN function works:

Concatenate Excel Ranges using the Textjoin function

In the above example, a space character is specified as the delimiter, and it combines the text strings in A1:A5.

You can read more about the TEXTJOIN function here.

Have you come across situations where this can be useful? I would love to learn from you. Do leave your footprints in the comments section!

You May Also Like the following Excel tutorials:

  • Sufiyan says:

    Hi,

    Does anyone know a way to do the following:

    Concatenate the values of several cells into a single cell and separate them with any delimiter of your choosing.

    Project Name Result
    Project1 Mike Project1, Mike, Neal, Peter
    Project1 Neal
    Project1 Peter
    Project2 Mike Project2, Mike, Neal, Peter
    Project2 Neal
    Project2 Peter

  • Brian Zammit says:

    Thanks, nice little function. Also adapted to use the ‘Len(Separator)’ value:

    Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String

    Dim Cell As Range
    Dim Result As String

    For Each Cell In Ref
    Result = Result & Cell.Value & Separator
    Next Cell

    CONCATENATEMULTIPLE = Left(Result, Len(Result) – Len(Separator))

    End Function

  • David says:

    This is a great post and will save me tons of time. Just one query, how would i set up the VBA code to ignore #N/A entries? I want to run the code from a pivot table and the number of results from the pivot changes each time?? Any help would be great. Thanks…..

  • Gabor says:

    Perhaps you could implement the TEXTJOIN function from Google Spreadsheet. Here’s my implementation:

    Function TEXTJOIN(separator As String, skipEmpty As Boolean, Ref As Range) As String
    Dim i As Integer
    Dim tmp As String
    For Each Cell In Ref
    If (Cell.Value “”) Then
    tmp = tmp & Cell.Value & separator
    End If
    Next Cell
    TEXTJOIN = Left(tmp, Len(tmp) – Len(separator))
    End Function

  • Darklight says:

    Thank you for these solutions!
    I would have like to alter the VBA method as to avoid empty cells (i.e. so the user can select a whole column, yet concatenate only non-empty cells).
    I know it is not trivial – but it would help to make this a more robust function.

  • Koen Stabel says:

    Hi,

    Does anyone know a way to do the following:

    I want to combine or concatenate text in every cell in column A with every cell in column B without repeating or flash fill because that won’t do the trick..
    Example here:

    COLUMN A contains:
    A
    B
    C

    COLUMN B contains:
    10
    20
    30
    40

    What I want as output in another COLUMN:
    A10
    A20
    A30
    A40
    B10
    B20
    B30
    B40
    C10
    C20
    C30
    C40

    Anyone an idea how to do this?

  • Cornan says:

    Apparently TEXTJOIN is associated with Office 365, NOT Excel 2016.

    TEXTJOIN() is NOT available on my desktop version of Excel 2016.

    • Sumit Bansal says:

      Hey Cornan.. You’re right! I have edited the tutorial accordingly.

      • adriana galiano says:

        Hi Sumit, im in need to of 2018 leave tracker template. Could you share updated excel version to [email protected]

  • James Golding says:

    Thx for this, perfect replacement for the MCONCAT formula from the now defunct (for anyone on 64bit OS) morefunc add-in

  • Shakeel Ahmed says:

    Cool! I was finding for auto update concatenate. Thanks for VBA code.

  • Kevin says:

    This is great – thanks!

    Would suggest one change to the VBA code – instead of using
    CONCATENATEMULTIPLE = Left(Result, Len(Result) – 1), you can use
    CONCATENATEMULTIPLE = Left(Result, Len(Result) – Len(Separator)); this will allow for multi-character separators.

    • Gabor says:

      … and allows even an empty string as a separator. A nasty little bug 😀

  • Julius Strickland says:

    This completely saved my ass today creating contact list spreadsheets to import elsewhere!!!!

  • Ro Lan Do says:

    Excellent! This did exactly what I needed it to in combination with Dynamic Ranges.

  • Jason Simons says:

    Theres a bug in the code. ExcelConcatenate is not equal to CONCATENATEMULTIPLE you should set CONCATENATEMULTIPLE =

  • Alok Morya says:

    Thanks for sharing such wonder ful trick.
    How do you do the reverse, plz suggest.

  • Thiyagarajan says:

    Very Very time saving & Interesting brother, Nice tips

  • Gun says:

    Can you add sample document?

    Thanks…

  • Golly says:

    That is a really cool solution. Will save lots of time. What is the more advanced method that automatically removes the curly brackets?

    • Sumit Bansal says:

      The more advanced way would be to use two cells. In one cell you would use the F9 key and get the hard coded values, and in another you can have a formula that would automatically remove curly brackets (using replace/substitute). You can go that way if you want this to be partially dynamic. But i would say the one mentioned in the article is easier and faster way.

  • Govind says:

    Very cool. I always had this problem.

  • >