Forum Discussion
Name seperation
Depending on if other people are going to be looking at it (usually why workplaces prohibit VBA), it's probably best to keep it simple.
Harun24HR's response is a great dynamic spill range solution, but if it's a collaborative sheet with less proficient users, you may want to reduce it to just a textbefore column, and a textafter column:
Threw in a hyphenated surname just to illustrate that won't impact anything. Both formulas are looking for the last instance of a space (-1), and extracting text before/after accordingly.
You need TEXTBEFORE() and TEXTAFTER() functions. To make it dynamic use TRIMRANGE() function or trimrange feature with REDUCE().
=DROP(REDUCE("",C.:.C,LAMBDA(a,x,VSTACK(a,
HSTACK(TEXTBEFORE(x," ",-1,,,""),TEXTAFTER(x," ",-1,,,""))))),2)
This did work but I'm worried other users will mangle it, I have some less excel savvy people using the workbook. Thank you.
