- Combine data with the Ampersand symbol (&) Select the cell where you want to put the combined data. Type = and select the first cell you want to combine. Type & and use quotation marks with a space enclosed. Select the next cell you want to combine and press enter. An example formula might be =A2&'.
- Option Explicit Public Function Join(rng As Range, delimiter As String) As String Dim cell As Range For Each cell In rng Join = Join & cell.Text & delimiter Next cell Join = Left(Join, Len(Join) - Len(delimiter)) End Function Let's break this down to understand what's going on. Public Function Join(rng As Range, delimiter As String) As String.
There are times where I need to join cells in Excel and separate them with acomma or some other text. I usually need this to make a SQL query or somethinglike this. Whatever the case may be, Excel doesn't seem to have an easy way tojoin multiple cells with a delimiter like a comma. So I've created a very simpleVBA function to help us with this task.
Combine multiple rows to one cell with Kutools for Excel The Combine Rows, Columns or Cells without Losing Data utility of Kutools for Excel can help you to easily combine multiple rows to one cell, and separate each combined text with certain separator. Before applying Kutools for Excel, please download and install it firstly. It's a very useful and yet simple procedure to merge multiple rows into a single cell. You can run the macro and get results for 3, 4, 5 and more rows of data, for a specific code (see the above image). Just remember one thing, the product codes must be in the following rows, in a sequence. ← Previous Next →.
The Goal
If you had cells A1:A3 like so:
Combine Two Cells In Excel First Name Last Name
And you wanted to join the text with a comma between them, one way you can dothat is by using a regular formula where you concatenate the cells withampersand (&):
However, this can become a lot of typing, especially if you have many cells youneed to put together.
We can make this easier by creating a custom function in VBA that we can use inour spreadsheet.
[alert heading='Note' type='info']There is no Join() function in Excel formulas,which is why we need to create it using VBA.[/alert]
The function will result with the same output, but with less typing:
Let's create the VBA function Join() so that we can use it in our spreadsheet tomake this task a loteasier.
If this article helps solve your problem, please consider supporting me because it takes a lot of effort (and coffee!) to provide this content.
👇 There's a special gift for you in return for your support.
Enjoy the post!
Join Cells in Excel with a VBA Function
Let's write a quick function you can write to help you join multiple cells witha delimiter. If you're not very familiar with VBA, please see myIntro to VBAarticle.
Let's break this down to understand what's going on.
How To Add Multiple Cells In Excel
The function accepts a Cell Range as its first argument, then a string as itssecond argument. The function will result in a single String that will bereturned to the calling Cell that uses the formula.
Here's the next part of the function that actually builds the resulting string:
Here we loop through each cell in the rng variable and we start building thefinal string by using Join =. Notice that we are using the name of theFunction here. You can think of this as an implicit variable that Excel will usethat gets returned back to the spreadsheet.
Here, we build up the string cell by cell. If we had the cells:
And used this formula:
Here would be the breakdown of how the function works.
A1
At this point, Join = ', cell.Text = 'one', and delimiter = ','.
So this line:
Turns into:
A2
Right now, Join = 'one,', cell.Text = 'two', and delimiter = ','.
The next time we go through the line in the For Each statement, it turns intothis:
A3
And in the third time we go through the loop:
Now the loop has finished, and we have Join = 'one,two,three,'. However, wedon't want that last comma there, so we need to get rid of it. That's when weexecute the next line:
Which turns into:
If you're not familiar with the Left(string, length) function, it's verysimple. It will take a string you give it, and only keep the amount ofcharacters you specify. So, Left('one,',3) will take the string 'one,' andonly keep the first 3 characters (from the Left) and end up with 'one'.
Also, the Len() function just figures out the Length a string has. SoLen('one') will return 3, for 3 characters. (Len is short for Length).
Excel Combine Multiple Cells In One Cell
So why not just use Left(Join, Len(Join) - 1)?
The reason for this is if you don't want a single character as a delimiter.Maybe instead of a comma, you want a specific piece of text between each string.For example, you could do:
Here, right before the last line we would end up with:
If we used:
We would end up with:
Basically, we would only get rid of the last space at the end. This is why weshould use the Length of the delimiter to remove from the end of the text. Withthat in mind, here's what would happen at the end of the function:
[/text_output][text_output]
Excel Combine Text From Two Cells
Putting the New Formula to Use
To use the formula, simply type it into a cell like a regular formula:
And you're not limited to a single character for a delimiter. You can have anentire word if you want:
Resulting in:
I hope you found this post helpful. If you have any questions or have found atweak to this function you'd like to share, I'd love to hear about it in thecomments below!
