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.
Yes, that would be a more efficient solution for that task - but the task is there to practise using concatenation so I hope a little artificial inefficiency is acceptable :)
Wouldn't =TEXT(C6,"dddd, dd mmm yyyy") be quicker and simpler in this case?
Hi Gary - no, the two features are identical. Some find it easier to read the formulas in the CONCATENATE mode but I think the & version is the simpler.
Are there any benefits of using CONCATENATE over &? Are there any situations where CONCATENATE works but & doesn't?