Excel is good for handling data, but there are times when you want to combine cells into one value or to split a string to make the data more manageable. Excel has several features which give us the ability to merge and split cells in multiple ways. Aug 24, 2020 The next way on how to combine cells in Excel can be done through the right-click context menu of the mouse. Select the desired range of cells Step 2. Mar 26, 2021 Generally, Excel enables you to combine data in two ways: you can either merge cells or concatenate their values. The first option means turning multiple cells into one. As a result, you get a single large cell that is displayed across multiple columns or rows.
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.
If you had cells
A1:A3 like so:
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!
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.
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.
At this point,
Join = ',
cell.Text = 'one', and
delimiter = ','.
So this line:
Join = 'one,',
cell.Text = 'two', and
delimiter = ','.
The next time we go through the line in the For Each statement, it turns intothis:
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”.
Len() function just figures out the Length a string has. So
Len('one') will return 3, for 3 characters. (Len is short for Length).
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:
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:
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!
We use Excel sheets for different kinds of projects. If you want to merge cells in excel or combining text from one or more cells is vital when we are editing it. Sometimes we may lose data in this process. Here we guide you with simple steps on merging data cells without losing any data. Let us get started.
Text from multiple cells can be combined into one cell by using the ampersand (&) operator.
To combine the text in two cells A1 and B1, steps to be followed are as follows:
To merge the cells in excel, Merge and Center option can be used in the home tab. But using this option, cells can be combined but not the text.
When we select the cells and use the “Merge and Center” option, it will keep the writing from the leftmost cell and remove the text from all other cells.
To merge the cells without losing text, we can use the “CONCATENATE” function. To merge cells without losing text, we can use the “Center across selection” option as an alternative to joining and Center option.
To combine the cells in excel, we have various options.
Excel concatenates multiple cells.
Concatenate function helps us to combine the content from multiple cells into one destination cell without retyping all the text. Steps to be followed to concatenate are:
Initially, we have to select the cell in which combined text to be placed. Then type the function into the cell, which starts with an equal sign (=) followed by concatenating.
Arguments can be entered in two ways,
First, we have to open the parenthesis, type the cell references (A2, B2) separated by commas and then close the brackets at the end.
=CONCATENATE (A1, B1). Otherwise, we can click on the A2 cell, type a comma, then click on the B2 cell and then type the closing parenthesis.
If you see an alert message, click on yes.
Now we have to press ENTER after adding the cell references to the concatenate function.
No space is found between the combined texts as the concatenate function combines precisely what is in the arguments.
You can also use =A1&B1 to merge shells data
=CONCATENATE (A5, “”, B5)
To fill sequential data into Excel with the fill handle, we need not type the function in every cell in the column. We can quickly copy the function to the other cell.
The quick and simple way to combine two or more cells vertically is to use the built-in merge option combined with center option.
Align the text after merging.
Also Read: How to Merge Excel Files and Sheets
Several reasons make cells unable to be merged
The most common reasons are:
When you are cell is in a table, you have to merge it before creating the table.
When your cell is in a protected cell editing is denied
In such cases, we need first to unlock the sheet and then go for the merging option.
There are many other problems that will not allow merging the cells.
Combining the cells using & symbol
There are some problems in which merging becomes an issue.
We need to take care of the problems accordingly and later merge them.
You can merge cells horizontally and vertically into a single cell.
Aligning is the crucial point here, and do not forget to get the data to the center.