Textjoin is a new handy formula in my frequent used list

By Lu


 

In my line of work, I handled a lot of lines of data extracted from a database, and sometimes, I just wanted to filter the extraction criteria based on a smaller list.

While I used to save this criteria into a text list, and load the text into the filter, for some reason, that method stopped working because the macro used cannot open and recognize txt file anymore.

But filtering based on string is still working. I am not going to type in a few hundred characters each time I want to filter. Instead, I used TEXTJOIN, a new formula that came with the Excel 365, joining together everything in an area into a continuous string.

= TEXTJOIN(“,”,TRUE,A:A)

Here, I am using a “,” to separate each different data, ignoring black space, and joining all the data within column A.

I pasted the resulting string back into my extraction query. Simple as that.