How to use Concatenate in Excel

In the previous instalment of Sunday Skills, I shared how to use Text to Columns in order to “split” columns of information. Today, let me demonstrate the “opposite”, using the Concatenate function in order to “merge” multiple cells, with text and/or numbers, into a single cell.

This will be useful if you have a first name and a family name in different cells for example, but you need a “full name” information for a compensation report. I will show you how to merge these 2 content cells with a space (expressed as ” “) in order to create the information you need.

So, the first step is to select Concatenate, which is a function in Excel. Go to the cell where you want to see the result. In the Formulas tab of Excel 2010, you will see “Insert functions” . It will be in a different area if you use earlier versions of Excel, but in all of them, the symbol for Functions is fx. Select it and type in “concatenate”.

Step 2 is about selecting all components that need to merge to create your end result. Enter each element under a separate text field. In this example  Text 1 is the cell with the first name (click on your first name cell), Text 2 is the space you need between first and family name to read the full name properly. Enter it as ” “. And finally text 3 is the cell with the family name (click on the family name cell).

In step 3, click OK and you are done. The full name will be displayed where you want it.

For those who learn better through visuals, I have created a Powerpoint overview of the steps to follow :

Other ways to use Concatenate include :

• Creating a full year in YYYY format from a YY format : concatenate “20” in Text 1 with the YY cell in Text2.
• Creating a DD/MM/YY from 3 separate fields : concatenate them with a “/” in between. Whenever you concatenate text or numbers, Excel will automatically insert the “” symbols if you forget to use them, but don’t forget to check what the end result will look like at the bottom of the wizard to make sure it is getting to what you need.

Let me know if you found this useful !

Related post :

How to use Text to columns in Excel

Copyright secured by Digiprove © 2017 Sandrine BardotSome Rights Reserved