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.

The Goal

If you had cells A1:A3 like so:

Data

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.

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:

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).

Cells

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]

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!

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.

How to Combine Text from Multiple Cells into One Cell?

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:

  • We have to select the cell in which we want to place the combined data.
  • To start the formula, we have to type an equal sign =
  • Then we have to click on the first cell, i.e., A1, then type the ampersand operator (&)
  • If space is needed between the text of multiple cells, type the text string “(double quote, space, double quote).
  • Now click the second cell, i.e., B1 and press enter to complete the formula.

How to Merge Cells in Excel?

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.

How to combine cells in Excel?

To combine the cells in excel, we have various options.

  • We can use merge and center option
  • We can use the ampersand (&) operator.
  • We can use the CONCATENATE function.

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.

=CONCATENATE.

Arguments can be entered in two ways,

Combine Data In Cells In Excel

Method 1

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.

Combine cells excel formula

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.

Method 2

You can also use =A1&B1 to merge shells data

Method 3

=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.

Excel merge cells vertically without losing data

The quick and simple way to combine two or more cells vertically is to use the built-in merge option combined with center option.

  • First select contiguous cells you want combine
  • Select the alignment group from the home tab.
  • Now click on the merge and Centre button.
  • When we click the button, the cells start to combine into a single text
  • There are other options called Merge across Merge cells that work similarly.

Align the text after merging.

  • First, select the text you wish to align with.
  • Now select the desired alignment from the alignment group.
  • This is found on the home tab.

Also Read: How to Merge Excel Files and Sheets

Why can’t I merge cells in excel

Several reasons make cells unable to be merged

The most common reasons are:

  • The cell is in a table
  • The cell is in a protected sheet

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.

They are:

Combine Data In 2 Cells In Excel

  • When the cells have data in the form of image
  • When the entered data is massive, merging and aligning becomes a significant issue.

Merge data in excel

Combining the cells using & symbol

  • First, you need to select the cell in which you want to put the combined data
  • Now type = and select the first cell that you wish to combine.
  • Now & with the help of quotation marks enclosing space
  • Now select the next cell you wish to combine and click enter.

Combine Cell Contents Excel

Combining using the concatenate function

  • Here you have to select a cell where you wish to put the combined data.
  • Now type = CONTACT(
  • Now you need to select the second cell which you wish to combine by using quotation marks, spaces, commas, or other text.

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.

Coments are closed

Most Viewed Posts

  • Tor Onion Iphone
  • Sigil Hecate
  • Ios 13 For Iphone 6
  • Tableau Server Prep
  • Tor Browser Setup

Scroll to top