Categories
Excel Training Hints & Tips

Using the Concatenate Function in Excel

The concatenate function quickly links the content of two or more cells

The concatenate function in Excel allows you to join text strings from different cells (like first name and last name) which can be very useful. It can be used with a number of other Excel functions.

You can concatenate in Excel by using the Concatenate function or use &. First we will have a look at the Concatenate function.

Step 1 Select the cell in which you want to concatenate text strings. You can type the formula =concatenate(B3,” “,C3) and click OK. You can also open the function dialogue box shown below and add the information.

concatenate1

Step 2  Click OK and the text string will be concatenated. If you need a blank space between two text strings you can do it by using ” ”  (quotation mark + space + quotation mark) for one of the text values (see example above).

You can also concatenate text strings by selecting the cell where you want text strings to be concatenated and type = the cell reference & ” ” & the second cell reference & ” ” & the third cell reference etc.

concatenate with emp

Nest the concatenate function in other useful functions.

If you work with the VLOOKUP function you may need to lookup a value based on information from two or more cells then you can just nest the Concatenate function in the VLOOKUP function or if you want to test values from two cells in an If function you again can concatenate values from the two cells.

In the example below the full name needs to be tested in an IF function. The Concatenate function is nested in the Logical_Test in the IF function.

concatenate if

In the example below the full name needs to be found in the commission table to return the correct commission rate.. The Concatenate function is nested in the Lookup_Value in a VLOOKUP function.

concatenate vlookup

 Related resources:

How to use a VLOOKUP function in Excel VBA

The concatenate function

How to Create Better Excel Spreadsheets: Part Two

By Richard Bailey

I love what I do; I get to work with an outstanding team to help hundreds of people with their challenges. I’ve learnt a lot from the teams I’ve worked with, no matter the size or industry we all have challenges to overcome, difficult customers, creating a budget or keeping a project on track.