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

Add to Favorites

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.

ceda06fb1fe2f51f6b74e41eb46d74ad.png

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.









How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Tutorial Details
Downloadable Files: Excel File
Excel Forum