Forum Discussion
Name seperation
I'm trying to seperate names into two columns where some people have double first names and some don't.
For example:
Full
Sarah Anne Smith
John Smith
Lilly Rose Plant
Jane Austen
I can easily get last or first, but I can't figure out how to get First Midde in the column without geting First Last for all the people who don't have middle names.
It needs to be a formula as names keep being added and I want it to do it automatically.
I was hoping I could find a formula that would grab the first two names before a space but can't figure out how to tell it not to grab the last name if there's only one space.
I've attached a file of what it would look like if it worked. I can't have it be VBA/Macro for bizzare work reasons.
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.
5 Replies
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.
This worked perfectly and is simple enough to be idiot proof, thank you!
You need TEXTBEFORE() and TEXTAFTER() functions. To make it dynamic use TRIMRANGE() function or trimrange feature with REDUCE().
Screenshot of the formula.=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.
I used Flash Fill on you sample data and it worked 100%.
