STOP Splitting Cells in Excel like this. Do this instead.

[@bryancamareno2831]

Great video 👍🏾 I didn’t know TEXTSPLIT could be used to split text to a table layout. I’ll be using that technique next time I run into a problem like this.

[@PaulEBrownbill]

Very good video, I like text split especially when splitting my columns and rows. thanks Paul

[@Kamyl101]

Ok, but what would you do if you had data with people with middle name and without middle name?

[@gsgrady]

In the case of a group of names containing first-middle-Last and first-last, you can use a conditional function to calculate the number of delimiters in the string and take appropriate action based on its result. For example if there are 2 delimiters (spaces), use textsplit. If there is only one delimiter, use the textbefore and text-after functions

[@Kamyl101]

@@gsgrady Sounds good, thanks :)

[@gsgrady]

@@Kamyl101 You're welcome. So in the case of a Full Name in cell A2 that may or may not have a middle name the formula would be: =IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=2,TEXTSPLIT(A2," "),TEXTSPLIT(TEXTBEFORE(A2," ")&" "&TEXTAFTER(A2," ")," "))

[@Gruhayogam90520]

Exllent video

[@francosilvestri123]

It doesn't work with Excel 2021. The TEXTSPLIT formula is not supported.