Excel Pro Tricks: Dynamic Excel Formula to Restructure Excel data with TEXTSPLIT & TEXTJOIN Function

Learn how to restructure or reshape Excel data or array using dynamic array formulas using simple to understand Excel functions without the need of power query, copy/pasting or manually typing the data.
In this Excel tutorial, we learn how to reshape, remodel or restructure Excel data using combination of TEXTJOIN and TEXTSPLIT function to split delimited data in one column to multiple column all using a single dynamic array formula in Excel.
Best thing about this approach is that output is dynamic that is output updates itself automatically every time source data changes.
Special thanks to Aditya Kumar Darak for the help and input in this example. His idea of using multiple delimiters in TEXTJOIN function made me realize that the last delimiter given in the string is used to append multiple rows of array which can later be used to split down the rows using TEXTSPLIT function. You can reach out to him on his linkedin profile here: / acoaadecv4mbiyyf69rzpc...
Lets Excel!
Access or Download Practice workbook for this Excel tutorial:
1drv.ms/x/s!Ap...
For more Excel tips, tricks and tutorials don't forget to subscribe to ‪@LearnExceltoexcel‬ YT channel.
Learn #Excel your way:
===================
Website: learnexceltoex...
--------------------------------------
Pick your favourite social page:
Fb: bit.ly/3yM3dvd
Tiktok: bit.ly/3wFllpf
Tw: bit.ly/3wGCRYU
Pin: bit.ly/3NsjKIU
Yt: bit.ly/3wxKL8a
Insta: bit.ly/3wtT9oW
Music by: / ikson
#excel #exceltips #exceltricks #exceltutorial #exceltutorialforbeginners #excelformula #excelfunctions #excelformulasandfunctions #excelformulatypes #exceltable #exceldata #data #dataentry #dataentryjobs #dataentryspecialist #textsplit #textjoin #datarestructure #datamodeling #datamodel

[@manjulakarthik2157]

in second column we use text to column option under data tab

[@dmcindianfan5374]

How do you remember this I can't even remember this for long time even if I ever try to memorize 😢😊

[@siddharthmhaskar4420]

Please explain what is the array used in delimiter place in text join
I think , for column and | for line break but please explain

[@surajverma6006]

Very nice

[@ericrobbins6953]

Excellent! But why join with that series specifically? Wouldnt any unique deliminator function the same?

[@deepanchakravarthy1116]

Super ❤

[@LearnExceltoexcel]

🤩🤩🤩

[@ohayo4699]

This case can't use only textsplit? why use both textsplit and textjoin together

[@LearnExceltoexcel]

To get both names and sales data processed in one formula in one go!

[@hicham2611]

​@@LearnExceltoexcelhow can i downloded

[@Channel-ri1rz]

How to download from description?

[@Softwaretrain]

This will not work for big data since Textjoin has limitation.

[@LearnExceltoexcel]

Please enlighten. Would love to know and everyone will benefit

[@Softwaretrain]

@@LearnExceltoexcel Textjoin function can handle up to 252 text arguments and the resulting string can contain a maximum of 32,767 characters, which is the cell limit in Excel. If this limit is exceeded, a TEXTJOIN formula returns the #VALUE! error.

[@Softwaretrain]

​@@LearnExceltoexcel The function can handle up to 252 text arguments and the resulting string can contain a maximum of 32,767 characters, which is the cell limit in Excel. If this limit is exceeded, a TEXTJOIN formula returns the #VALUE! error.

[@farooqtahir538]

Without explanation they are useless.No one is going to memorize them