Learn 5 methods to split values in a column and learn how each method is best used and when to use them. Videos: EMT video 1904. Method: TEXTSPLIT. When to use: Use formula when you need instant update EMT video 1905. Method: Text To Columns. When to use: Use for one time event EMT video 1906. Method: Power Query. When to use: Use when importaing data EMT video 1907. Method: TEXTJOIN & TEXTSPLIT. When to use: Use when you need single cell formula to spill an array of arrays and there is less than 32767 characters EMT video 1908. Method: LAMBDA & REDUCE. When to use: Use when you need single cell formula to spill an array of arrays and there is more than 32767 characters
Curated by: excelisfun (5 videos)
Download Excel File: https://excelisfun.net/files/EMT1904-08.xlsx Buy excelisfun’s Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Full playlist of splitting videos: https://studio.youtube.com/playlist/PLrRPvpgDmw0kvGUtN3lRyNbiCmYURc9_m/videos Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Learn about the power of Excel’s REDUCE & LAMBDA functions to recursively and iteratively run a function over an array and deliver a final rectangular range of values split from a column of delimited values. Learn about the relationship between the REDUCE and LAMBDA functions and how they work together to accomplish the near impossible in the Excel worksheet. Topics: 1. (00:00) Introduction. 2. (00:03) Five Total Videos: 1904, 1905, 1906, 1907, 1908. 3. (00:48) Cell Character Limit of 32,767. See how TEXTJOIN formula fails when the number of characters get above 32,767, But the REDUCE & LAMBDA functions have no problem! 4. (01:59) Theory behind how the REDUCE & LAMBDA functions work to get TEXTSPLIT and VSTACK functions to spill an array of arrays. 5. (02:50) Fundamental problem of spilling an array of arrays. 6. (03:05) What does the REDUCE function do? What do the arguments in REDUCE require? 7. (03:42) What does the LAMBDA function do? What do the arguments in LAMBDA require? (04:07) What is relationship between REDUCE and LAMBDA? 8. (04:50) REDUCE, LAMBDA, TEXTSPLIT, VSTACK, IFNA and DROP functions to build formula. 9. (00:25) Split text horizontally and vertically? How to spill an array of arrays? 10. (06:57) Summary of REDUCE formula 11. (07:11) Summary of last five videos: EMT 1904, EMT 1905, EMT 1906, EMT 1907, and EMT 1908. 12. (07:31) Closing This video has subtitles or audio translations in these languages: Afrikaans, Albanian, Amharic, Arabic, Bangla, Bosnian, Bulgarian, Burmese, Chinese, Chinese (Hong Kong), Danish, Dutch, Dutch (Belgium), Dutch (Netherlands) (experimental), Estonian, Faroese, Filipino, French, French (France), Georgian, German, German (Germany), Greek, Hindi, Icelandic, Indonesian, Irish, Italian, Japanese, Khmer, Korean (experimental), Latin, Macedonian, Malagasy, Malay, Malay (Singapore), Malayalam, Mongolian, Nepali, Norwegian, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Portuguese (Portugal), Romanian, Russian, Scottish Gaelic, Serbian, Sicilian, Slovak, Slovenian, Somali, Spanish, Spanish (Latin America), Spanish (Mexico), Spanish (Spain), Spanish (United States), Swahili, Swedish, Tajik, Tamil, Tatar, Telugu, Thai, Tibetan, Tongan, Tsonga, Tswana, Turkish, Turkmen, Ukrainian, Urdu, Vietnamese, Welsh, Western Frisian, Zulu. Click gear icon below video to set subtitles, audio and language. #excel #microsoft #excelisfun #excelcourse #mikegirvin #excel365 #freeexcellessons #excelformulas #excelfunctions #lambda #excellambda #recuce #lambdafunction #reducefunction #lambdahelperfunction #arrayofarrays #dynamicspilledarrayformula #spilledarrays #Excellambda #arrayformulas #excelarray