What the function - TEXTJOIN in Excel + 3 Advanced Examples

Welcome to episode 2 of What the Function. We will learn about TEXTJOIN() function in Excel.
Use TEXTJOIN to combine a bunch of text values with delimiter in Excel. The simple usage is
=TEXTJOIN(delimiter, ignore_missing_values, your range of inputs)
For ex: you can use =TEXTJOIN(", ", true, A1:A5) to combine values in range A1 to A5 in comma separated format.
TEXTJOIN is an alternative to CONCATENATE function.
For more details and example file, visit
chandoo.org/wp/textjoin-funct...
#textjoin() #CombineTextValuesExcel

[@blackinktraining9810]

Great video. It's hard to believe there are so many un-tapped functions in Excel.

[@sarva0483]

this is like pearl inside the shell ! thanks for letting us know.

[@zakaria9821]

Effective as usual! Thanks!

[@rajanghadi2337]

I used =MID(H4,SEQUENCE(LEN(H4)),1) to split each character in rows and =MID(H4,SEQUENCE(,LEN(H4)),1) to split each character in columns. Thank you for this video

[@chandoo_]

Yes, I would use SEQUENCE too as it is easier than 21-ROW() technique.

[@rahulchopra2030]

Brilliant explanation sir . A good alternative for concatenate

[@wayneedmondson1065]

Hi Chandoo.. thanks for this fun video on some different ways to use TEXTJOIN. I like the text reversal formula with MID and SEQUENCE. Great ideas.. Thumbs up!!

[@maheshsanghani6380]

Sir Thanks for teaching in a nice way .

[@PaulaWeb3andCrypto]

awesome - great video, and the weather looks awaesome

[@chandoo_]

It has been a great week so far. :)

[@dhunpagla3871]

Mr Chandoo you are really Khurapati in excel 👌🙂

[@chandoo_]

Not sure if that means crazy... but I will take it :D

[@dhunpagla3871]

@@chandoo_ you are absolutely correct Mr Chandoo ...it is crazy 🙂👌 & suits you 😎

[@mguru1982]

as usual, u r awsom

[@SandeepSharma-md2ex]

Pls make a video how can I subtotal only negative values in using subtotal formula

[@SandeepSharma-md2ex]

Awesome like you..

[@90hsilva]

Very good tips, thanks

[@chandoo_]

Glad it was helpful!

[@gagansingh3481]

Awesome Chnadoo

[@chandoo_]

Thanks 🤗

[@hazemali382]

Wow more than great ♥

[@chandoo_]

Thanks Hazem.

[@The_1two3]

U r really awesome 😎.

[@chandoo_]

Thank you so much 😀

[@taizoondean689]

Thanks bro

[@chandoo_]

Welcome

[@redhaakhund1271]

Supper 👍👍👍👍👍

[@chandoo_]

Thanks 🤗

[@kanchanprajapati7228]

Please provide the practice materials for this topics

[@joedaguro]

Very helpful, i need help on my data on network folder: (1) when I move to another pc station, it returns connection error unless I manually edit (2) rows is mixed with rows of either comments or numbers but independent cell (note: they are usually in same column), i need the numbers only. Thank you👌🙏

[@chandoo_]

You can use parameters in PQ to set folder path. For cleaning data, just use split or extract options in PQ. See this video if you are new to PQ and want to understand how to use it - kzbin.info/www/bejne/hpqpcpSVn8-Ue6s

[@manishtare]

I am using flashfill feature insted any other commands. That is more convenient to use.

[@chandoo_]

Flash fill is very easy to use, but manual. So if your data changes, make sure to press CTRL+E to redo the flashfill.

[@saxena1955]

Hi Chandoo, Coded Output How we can do it in Office 2013 as array function is not available there?

[@eloytama9193]

Wonder if anyone knows how to text to join certain rows that are excluding a certain highlight [im filtering them to show green] currently am able to get all of the rows/columns but unable to exclude certain ones if unchecked

[@hemanthkumar4870]

Wow... very useful Apart from that..I love your beard...

[@chandoo_]

Thanks! 🧔🧔🧔

[@venkatiyer7459]

Excellent as usual Sir.... I am using Excel 2013... your trick for mid with row() is not working for me ... I don't know where I am making mistake... for me it's homework as such ... Thanks

[@chandoo_]

Hi Venkat.. Thank you. You can use MID & ROW to convert text to an array in 2013, but you will not be able to use TEXTJOIN as it is a new function in 2016 Excel.

[@venkatiyer7459]

@@chandoo_ Sir I tried my best ... copied the formula =MID(B6,{1,2,3,4,5,6},1) but I am getting only one character and not all the characters in each column as shown by you .... whether this has any reason with my 2013 instead of 2016 ... Please advice..

[@chandoo_]

Hi Venkat... In all other versions excel Excel 365, you will only get one character. The spill behaviour and dynamic array functionality is only part of Excel 365. In older versions, to get all the values, you select a range of cells, type the formula =MID(B6,{1,2,3,4,5,6},1) in first cell and press CTRL+Shift+Enter.

[@venkatiyer7459]

Chandoo Thanks Sir

[@venkatiyer7459]

@@chandoo_ Thanks a lot .. It worked with CSE

[@ursvenky6394]

Hi chandooo How can we do that in VBA please. I've been trying to get it on my own but no luck. Could u help me ?

[@chandoo_]

You can use the CONCAT UDF for this. Here is the code that I shared all the way back in 2008. chandoo.org/wp/how-to-add-a-range-of-cells-in-excel-concat/

[@spyxxx566]

is TEXTJOIN only available in Excel 365 and not 2016?

[@chandoo_]

You need Excel 2019 or 365 to use Textjoin

[@daae_rion]

Hello!! thank you so much for this video. Is there a way to exclude duplicate values? So if for some reason a person was listed twice and you only wanted their name returned once in the "Members" column? thanks again!!

[@chandoo_]

You can use the newly introduced UNIQUE function to do this. See this page for more info on dynamic array functions like UNIQUE, FILTER etc. chandoo.org/wp/dynamic-array-functions/

[@mirrrvelll5164]

Link doesnt work....

[@chandoo_]

Please try again - chandoo.org/wp/textjoin-examples/

[@mirrrvelll5164]

@@chandoo_ It does work =) Thx for this amazing idea!