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

Sections:

Syntax

Create the Function and Install It

Usage Examples

Notes

Syntax

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

=Combine_Values(selection, separator_input)

ArgumentDescription
Selection

Selection of cells that you want to be combined.

Separator_input

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

 

Next

 

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.

Result:

Combine Part Numbers

Here is the sample:

Combine the values with a dash between each part.

Result:

Notes

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 First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
Macro: This free Excel UDF (user defined function) returns the first word from a cell in Exce...