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 - HYPERLINK 
TOTW index
Next post - Introduction to charts

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.

Anonymous