Skip to main content

How to combine multiple cells in Excel

When working with text in Excel its often useful to combine the text from two or more cells into a single cell. This can be done with a couple of different methods

Method 1 - using the Concatenate function

I've previously blogged about the basics of this function in this post . Here we'll look at the function in a bit more detail and some common use cases.

Almost all people I've helped with Excel aren't familiar with the word 'Concatenate', its definition is "link (things) together in a chain or series." which is exactly what the concatenate function does.

Its simplest use case would be,

= Concatenate (A1, A2)

This would combine the values in A1 and A2 into a single value which would be displayed in the cell in which the function is placed, lets look at a real example

Here we have a list of first names and last names and we'd like to combine them into another column titled 'Full Name'. We'll begin by using the Concatenate function and passing the first and last names to it as the arguments (shown here by 'text1', '[text2]'…

As we add the cell references to the function they become highlighted with colour boxes to help display how the function will work

If we hit enter and fill down, we'll see the result of the function applied to each row. As you can see, the function has done exactly what we've asked it to do and combine the first and last names but without a space because there is no space at the end of the first name or the start of the last name. 

Adding the space can easily be done by using the " " text between the first and last name. To do this we need to modify the function by clicking on cell D2 and then pressing F2 (or editing the function in the formula bar)

Note - the additional text to combine (the space) needs to be preceded by a comma so its separate to the B2 reference. This is true for the last cell reference too. To summaries, we need to enter (B2, " ", C2) after the word concatenate so that each piece of text to combine is has a comma between it for the function to work correctly.

If we fill this function down we'll see the following:

Each cell now has the first and last name with a space in between them.

Method 2 - Using the Ampersand method

Although its advisable to use functions, it is possible to complete the same process but without using the concatenate function at all. The Ampersand (&) character in excel can also be used to combine two pieces of text.

If we use the same example from before, we can enter the following formula into the Full Name column

Which will return the following result

As per the previous example, there is no space added to the result so we have to manually add it in the same way as before making sure to include an ampersand between each of the 3 pieces of text to combine.

When we fill this down to the subsequent rows the result is…

Conclusion

Combining text in excel is a common activity that can easily be achieved using the Concatenate function or placing an ampersand between each piece of text to combine.

In the next post I'll look at how we can separate text strings.

Labels:

Comments

Post a Comment

Popular posts from this blog

How to combine multiple files with Power Query (with no VBA and just 10 mouse clicks!)

The need to combine information from multiple files is one that most users of Excel will have come across at some point in the use of Excel. I've personally spent far too many hours aggregating data from multiple files, that are identical in structure, so that I can analyse larger datasets and provide insights into products and processes. For anyone who has also done this and not yet discovered Power Query you'll probably be amazed how simple the process has become. I realise there might be some who will say "just use VBA, its easy once you learn how to code..." and they would be right. The method using Power Query provides a zero code solution that is an evolution of the Excel interface that many will already be familiar with. In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder. The folder contains only these files and i'd recommend you do the same if you're looking to try out this pr...
Post a Comment
Read more

Extracting data from Word (.docx) files into Power Query

Word and Excel don’t usually get along too well so it's no surprise that Power Query isn't directly compatible with its estranged cousin Word either. If you are presented with the need to import data from Word into Power Query you'll be please to hear it is possible however it requires a couple of manual steps to make it work.  The manual steps could fairly easily be completed by a batch file which would automate the process further. Here is the Excel data pasted 'as values' in a Word file which i'll use for the first example Here is the Excel data pasted with 'keep source formatting' which i'll reference a couple of times in the article. Although the steps I've covered below aren't complex, this whole process has some unknowns around it so you may find the result in your instance varies from mine. The Word file I've used contains the contents of a range of excel cells that I deliberately pasted as values into Word to create a test file f...
Post a Comment
Read more

How to automate the import of all files in a Google Drive folder to PowerBI, now updated, please read the first paragraphs!! (PowerQuery)

Update!!! This method no longer works although there is a new Google Sheets connector for PowerQuery that is currently in beta. If you're using PowerBI, you'll need to enable the preview features to enable it. https://docs.microsoft.com/en-us/power-query/connectors/googlesheets In the interest if demonstrating how it 'did' work, the original post is provided below. If you attempt to replicate this, you'll quickly realise the website doesn't behave as it used to. (here is the original article)  Using PowerQuery to access multiple files within the same folder on a local or network drive is a game-changing feature that will almost certainly save many people hours of effort. This functionality is great if your data exists in a place that is easily accessible but what do you do if your data is somewhere less accessible like Sharepoint, OneDrive or even Google Docs? I have previously connected to data on SharePoint and found it fairly straight forward which raises the...
5 comments
Read more