Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
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
- Skill Level: All Levels
- |
- 5 hours+
How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.
- Skill Level: All Levels
- |
- 2 hours
Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.
: Excel function that combines values from multiple cells or inputs using a delimiter - work...
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: Change or replace text in a cell with other text - you can replace a single character, num...
Tutorial: How to create and use a function that removes all text or all numbers from a cell, whichev...
