Joining values in Excel made simpler!

 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 Now  

About the Author:

Steve Wiggins  

Steve is a highly experienced technical trainer with over 10 years of specialisation in Software Application Development, Project Management, VBA Solutions and Desktop Applications training. His practical experience in .NET programming, advanced solution development and project management enables him to train clients at all levels of seniority and experience. Steve also currently manages the IT infrastructure for New Horizons of Brisbane, providing him with daily hands-on experience with SCCM, Windows Server 2012 and Windows 8.

Read full bio