How to Combine Cells in Excel

How to combine cells in Excel

In this tutorial, I’ll guide you through a simple way how you can combine contents of multiple cells within one cell. This means that the times of dull copying and pasting of data bit by bit are over!

And this is how to do it!

See the video tutorial and transcription below: 

See this video on YouTube:
https://www.youtube.com/watch?v=aVb8wP5iArY

We’ll use the following example data set – first names, last names, and countries, and we’ll end up having them all written out in one cell, one next to each other, in the rows of the column E.

How to Combine Cells in Excel - all in one cell

Although there are more ways to the same outcome, we’ll have a look at one, the simplest one only.

Simplest Way How to Combine Cells in Excel

First, select a target cell that should contain the final combined contents.

Then click on the option fx – ‘Insert Function’.

How to Combine Cells in Excel - choose function

A pop-up window appears where, in the box which says ‘select a category’, we’ll choose ‘All’.

How to Combine Cells in Excel - all functions

Look for the function ‘Concatenate’ and confirm with OK.

How to Combine Cells in Excel - find concatenate function

At this point, Excel will offer you only two boxes at first – Text 1 and Text 2, into which you can type the text you want to combine. More boxes are added automatically when you click into the last box. And if you still need some more – don’t worry! Excel will allow you to concatenate exactly two hundred and fifty-five text boxes like these.

How to Combine Cells in Excel - text windows

Alright then, let’s enter a first name into the ‘Text 1’ box here. We’ll click into the box with ‘Text 1’ and, right away, click on the cell which contains the data we want to use, in our case cell B3.

How to Combine Cells in Excel - first word

So far so good!

Now let’s say we want to add a space between the first and the last name. We’ll click into the box ‘Text 2’ and hit the spacebar to enter a single space. When we move to the box below (box number 3), Excel will turn this simple space into double quotation marks with the space located between them.

How to Combine Cells in Excel - put space between words

Well, let’s carry on!

The box with ‘Text 3’ should contain a last name, which means we’ll click into the box again and then click on the cell C3 which contains the ‘wanted’ bit of information. It might be very helpful to check this part over here – it displays how concatenated text will look like once done.

How to Combine Cells in Excel - concatenated text

If you need to add a comma after the last name, type in a comma into ‘Text 4’ box and it immediately appears in the preview, as you can see. The same way you can add a hyphen, colon or any character you need.

And the box ‘Text 5’ will be filled in with a country. To do this we’ll follow the same steps you’re familiar with now.

Once everything we need has been combined, click on OK.

How to Combine Cells in Excel - strings to be joined

Voila! The text has been linked together without tedious copying and pasting or even retyping!

How to Combine Cells in Excel -result of concatenated text

How to Combine Text in the Remaining Rows

In case you need to combine the text in the other rows, too, you don’t have to write out any functions or data again. Just hover with your mouse over the bottom right corner of the cell that contains the prepared function and drag it down through all the rows in which you want to use the ‘concatenate’ function.

Like this!

How to Combine Cells in Excel - combined text in more cells

And here’s an important tip before we wrap it all up.

How to Change any Data in the Cell With Concatenated Text

Once everything has been concatenated and when you click on a cell that contains combined text, you’ll see that the cell does not contain the text itself, but references to other cells as well as spaces and commas from the final text included in quotation marks, just as is needed.

How to Combine Cells in Excel - references and quotation marks

Therefore, if you want to change any data in the cell with concatenated text, you’ll need to make changes in the source cells that include this piece of information. For example, if we wanted to change England to the USA, we will do so in the column containing countries and the information will automatically change in the target cell with the final text.

How to Combine Cells in Excel - change data in source cells

Maybe you’re asking now how the concatenated text (not the formula) can be copied for further work into another cell as a simple text.

Don’t miss out the next video tutorial with us where you’ll learn:

If you’ve found this tutorial helpful, like us and subscribe to receive more videos from EasyClick Academy. Look at more tutorials that help you use Excel quick and easy!

See you in the next tutorial!

Comments

Add comment

Your email address will not be published.

Your personal information will only be used for the purpose of processing this comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed.