
Jan 19, 2017
One of the more common uses of Excel we see here at New Horizons is using Excel for data formatting and simple manipulation. With that in mind, there is often a reasonable amount of concatenation that occurs as a result of database systems spitting out single values in columns.
Whilst this stores very efficiently, it doesn’t look too good in reports, charts or pivot tables.
To join things together in Excel, you could use the CONCATENATE function. This function basically lets you specify the text you would like to join together in the order it needs joining.
For example, =CONCATENATE(A1,” “,A2,” “,A3) would take the contents of A1 and then put a single space on the end, then add the contents of A2, another space and finally adding the contents of A3.

For lengthier strings, this could become tiresome.
Good news! There is now a function called TEXTJOIN that makes this process much simpler. The syntax is =TEXTJOIN(delimiter,ignore_empty_cells, values).
So using the same content, we would only need to put =TEXTJOIN(“ ”,TRUE,A1:A3).

Another benefit to the addition of the TEXTJOIN function is that Microsoft have saved our keystrokes. There is a new function called CONCAT – however, the function is merely a new way of using the existing CONCATENATE function and both functions will coexist to maintain compatibility.
If you don’t see the availability of the TEXTJOIN or CONCAT functions, ensure your Office 2016 products are up to date!
Happy concatenating!
For more information, take a look at New Horizons' Excel 2016 training courses.
How do your Excel skills stack up?
Test NowNext up:
- Can you trust Marketing with your Social Media play?
- An Introduction to Windows Ink Workspace
- Tips to avoid creative burnout
- Master Document in Word – Part 2
- What you need to know to conduct a job interview
- The best facilitators use inclusive communication
- Photoshop: Using clipping masks for custom photo layouts
- Secrets of Excel dates and numbers
- How adults learn
- What is Bring Your Own Device (BYOD)?
Previously
- Can you trust Marketing with your Social Media play?
- What is OneDrive?
- Better together: Power BI and SQL Server Reporting Services (SSRS) 2016
- Insight into the database structure internals
- AD DS vs Azure AD – So what’s the difference?
- Is Social Selling anything more than just a buzzword?
- Secrets of Excel dates
- Great workplace trainers need to become great workplace assessors
- Animating with Adobe CC: Part 3 – Animating with symbols in Flash CC
- What can we expect from Microsoft in 2017?