The TEXTJOIN function was new to Excel in 2016 and it’s one of several concatenate type functions. I have to confess I had never used TEXTJOIN in reality until a delegate asked me this question this week:

I have numbers in a column like you see below … a number … one or more blank rows … a number … one or more blank rows … a number … and I want to combine all of those numbers in that column to look like this, eg, 1+2+3+4+5 … how can I do that?

This is an example of the problem

 

This is an example of the result needed

This is the solution

Use TEXTJOIN in A19 with the ignore_empty option set to TRUE and there you are. The delimiter does not have to be + … it’s in “” so you can put whatever you like! The range of values can be as big as you like.

There you are, no file to download because you can recreate this so simply by yourself.

Duncan Williamson

9th August 2017

 

 

 

 

 

 

%d bloggers like this: