Simple Excel Function to Combine Values in All Versions of Excel - UDF

Excel function that combines values from multiple cells or inputs using a delimiter - works for every version of Excel!

This function allows you to select ranges, select individual cells, input text, and input numbers and combine all of that data using a delimiter between each piece of data.

Download the attached workbook to get a working example of this formula; this workbook also contains an example of the TEXTJOIN() function, which is available in Excel versions from 365 and later, but don't worry, our User Defined Function (UDF) here works on all version of Excel! :)

Sections:

Syntax

The Custom Function - UDF Code

More Information

Notes

Syntax

How to use the custom function

=Combine_Values(delimiter,ignore_empty,text,...)

Argument	Description
Delimiter	The string that you want to go between each value that you combine. This can be a space or a dash or whatever symbol you want; it can also be a blank value if you do not want to have a delimiter between the combined values. The delimiter must be surrounded with quotation marks "-" and if you want to use a blank or no delimiter, use empty quotes like this "".
Ignore_Empty	TRUE or FALSE Controls if you want to ignore any empty cells. This is helpful when you are selecting ranges to combine where some of the cells may be empty. Usually, this value is set to TRUE.
Text	The values to combine. This can be a range of cells, a single cell selection, text or numbers. Put at least one value in for this argument and then put a comma after it and continue selecting cells, ranges, text or numbers to add to the data that will be combined.
[more text]	This represents the optional Text arguments that you can have. If you want to combine multiple ranges, cells or values, just keep selecting them, making sure to put a comma between each selection.

[] = optional argument

The Custom Function - UDF Code

Function Combine_Values(delimiter As String, ignore_empty As Boolean, ParamArray text() As Variant)
' TeachExcel.com

Dim piece As Variant
Dim element As Variant
Dim i As Long
Dim newArray() As Variant

' Counter variable
' This will help us build the array that will hold the output.
i = 0

' Loop through all of the values that we would like to combine.
For Each piece In text

    ' Check if the current value is a Range or not.
    If TypeName(piece) = "Range" Then
        ' If here, this "piece" is, in fact, a Range.

        ' Loop through the Range.
        For Each element In piece

            ' Check if the user wants to ignore empty cells.
            If Not ignore_empty Or Not IsEmpty(element) Then

                ' Resize the array so that it can hold the new output value.
                ReDim Preserve newArray(i)

                ' Add the current range's value to the array.
                newArray(i) = element

                ' Increment the value used to build the array.
                i = i + 1

            End If

        Next

    Else
        ' This value is not a Range.

        ' Check if the user wants to ignore empty cells.
        If Not ignore_empty Or Not IsEmpty(piece) Then

            ' Resize the array so that it can hold the new output value.
            ReDim Preserve newArray(i)

            ' Add the current value to the array.
            newArray(i) = piece

            ' Increment the value used to build the array.
            i = i + 1

        End If

    End If

Next

' Combine the output values from our array using the Join() function
' and output the result to the spreadsheet.
Combine_Values = Join(newArray, delimiter)

End Function

More Information

Install the UDF in a regular module.

If you would like more information about UDFs, how to install them, and how to use them, check out our tutorial for that:

Create a Custom Function in Excel - UDF

Notes

This is a more robust and complex version of another one of our UDFs:

Simple Excel Function to Combine Text with a Separator

Make sure to download the attached workbook for this tutorial so that you can view working examples and easily copy/paste the UDF VBA Macro code.

---