Simple Excel Function to Combine Text with a Separator

Add to Favorites

Excel function that makes combining text very easy. This function is simpler and better than the CONCATENATE function.

This function is a UDF and this tutorial assumes that you have a basic understanding of those. You can learn about them here: User Defined Functions (UDF) in Excel



Create the Function and Install It

Usage Examples



(UDF code for the function listed in the next section)

=Combine_Values(selection, separator_input)


Selection of cells that you want to be combined.


The separator that you want to go between each cell. This could be a space or a dash or whatever you need. Make sure to surround it with double quotation marks unless you use a number.

Create the Function and Install It

(If you do not know about UDFs, please visit the link at the top of this tutorial. Here, I assume you know what they are.)

Here is the UDF code:

Function Combine_Values(selection, separator_input)


ucount = selection.Cells.Count


loopcount = 1


For Each c In selection


    If loopcount < ucount Then separator = separator_input Else separator = ""


    new_value = new_value & c.Value & separator


    loopcount = loopcount + 1




Combine_Values = new_value


End Function

Copy/past the above code into a module in the VBA Editor (Alt + F11 and then Insert > Module) and then go back to Excel and to use it.

Usage Examples

Combine Text with a Space

Here is the sample:

Use the new function to combine it into one cell with a space separating each word.


Combine Part Numbers

Here is the sample:

Combine the values with a dash between each part.



This function is far superior to the CONCATENATE function in Excel.

If you didn't understand everything here, please read the UDF tutorial that is linked to at the top of this tutorial. That will get you up-to-speed on how these types of custom functions work in Excel.

Download the sample Excel file attached to this tutorial to get the UDF code and play around with the function.

Downloadable Files: Excel File

Similar Content on TeachExcel
Simple Excel Function to Extract a Word or Text from a Cell
Tutorial: Excel function that makes it easy to extract a word or text from a cell in Excel. This is ...
Guide to Combine and Consolidate Data in Excel
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Excel Function to Remove All Text OR All Numbers from a Cell
Tutorial: How to create and use a function that removes all text or all numbers from a cell, whichev...
Convert an Incorrect Date Format into a Real Date in Excel
Tutorial: Change an incorrectly formatted/input date into the proper date format for Excel. This tut...
Combine Values from Multiple Cells into One Cell in Excel
Tutorial: There are two easy ways to combine values from multiple cells in Excel. In order to do thi...
Extract the Last Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
Macro: This UDF (user defined function) extracts the last word or characters from a cell in Excel...