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.
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 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," ")," "))
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.
Ok, but what would you do if you had data with people with middle name and without middle name?
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
@@gsgrady Sounds good, thanks :)
@@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," ")," "))
It doesn't work with Excel 2021. The TEXTSPLIT formula is not supported.
Very good video, I like text split especially when splitting my columns and rows. thanks Paul
Exllent video