Excel Tip of the Week #70 - Revisiting CONCATENATE

Hello all and welcome back to the Excel Tip of the Week.  In this week's General User post, we are looking again at the most essential of text-editing functions, CONCATENATE.  This originally appeared in TOTW #14.

What it does: CONCATENATE, or &, can be used in formulas to join together two more pieces of text.

How to write one: There are two possible ways of writing this function:
     =CONCATENATE(reference 1,reference 2,etc.)
     =reference 1&reference 2&etc.
Note that each reference is listed separately.  You can’t write e.g. =CONCATENATE(A1:A5) – you must list each reference one after the other.

If you want to insert a space between each word, you can do this in either of two ways – putting the space into its own column, or adding it directly into the formula via speech marks (e.g. =A1&" "&B1).

What you can use & sign formulas/CONCATENATE for:
 - Joining first & last names into a single cell
 - Producing addresses from a list of house numbers, street names, etc.
 - Inserting a formula-calculated value into the middle of a sentence
 - Improving the functionality of VLOOKUPs
 - Formatting dates into a desired format

There are demonstrations of all of these as well as a practice task in the attached workbook.

Previous post 
TOTW index
Next post

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.

CONCATENATE examples and practice task.xlsx