Simple Excel Function to Combine Text with a Separator
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:
Create the Function and Install It
Syntax
(UDF code for the function listed in the next section)
=Combine_Values(selection, separator_input)
| Argument | Description |
|---|---|
| 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.
Question? Ask it in our Excel Forum
Tutorial: Excel function that makes it easy to extract a word or text from a cell in Excel. This is ...
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Tutorial: Formula to count how many times a word appears in a single cell or an entire range in Exce...
Tutorial: How to create and use a function that removes all text or all numbers from a cell, whichev...
Tutorial: Change an incorrectly formatted/input date into the proper date format for Excel. This tut...
Tutorial: There are two easy ways to combine values from multiple cells in Excel. In order to do thi...
