When you are working with data in Excel, you may often find yourself needing to combine the results of two or more separate cell formulas into a single cell. This process is called concatenation. In this tutorial, we will show you how to concatenate cell formulas in Excel and explain the power that concatenation can give you when working with data. We will also provide some tips on how to avoid common errors when using concatenation. Let’s get started!
Back To Basics Concatenation.
The syntax of this formula is
=CONCATENATE(TEXT1,TEXT2…)
Up to 255 text entries can be added to the function and each one of them should be separated by a comma. One point to note, CONCATENATE does NOT add in extra spaces between text, in order to do this, you need to accommodate these extra spaces within the formula.
Also note, you can also use CONCAT, TEXTJOIN or & in the same way. Use whichever you feel comfortable with OR what is available in your version of Excel.
So, don’t be put off by this CONCATENATE terminology. The concatenation of cells is one handy little Excel feature that is fabulous. Basically, it means combining the values of two cells. The great thing about Excel is that it has functions that work with arithmetic functions but it also ones that work well with text and Concatenation is one of them.
Concatenation Excel Example.
Heres an example of when you might find concatenate in Excel useful.
Suppose you have a first name in one cell and last name in another column, and you need to combine then in one cell for a mail merge or some other administration exercise. In this example I want to generate a username with the first and surname. The sample data set I am using is below.
There are actually a few ways to concatenate the information
Use the built-in formula in the new cell where you want the new information
=Concatenate and follow instructions on screen entering the first text, then the second. Hit return and voila your results are displayed.
So, to generate the username in this example, the formula is below.
=CONCAT(B3,” “,C3)
Notice the extra space added. Any extra punctuation including commas and spaces etc need to be manually added.
Alternatively there is another way way which some people find a lot easier. This is using the & symbol. Let’s suppose you have the same scenario where you want to combine first and last names in to one column. Typing the following will achive this Same result. Possiby a bit quicker.? What do you think?.
Excel is a powerful tool for organizing and analyzing data. With the concatenation feature in Excel, you can quickly and easily merge cells into one long string. This can be useful for creating labels or headers in your spreadsheet, or for combining data from multiple sources into one cell. The example used was to join first name and lastname in our data source with little effort. To concatenate cells in Excel, select the cells you want to merge and use the CONCATENATE function. We’ve shown you how to do this using both the standard formula and the quicker way using the & symbol. Feel free to browse our other Excel tips, videos, Ebooks and sign up to the Excel At excel Newsletter for three free tips every month.
More Excel Tips
If you want to see all of the blog posts in the Macro Monday series. Click on the link below
