I'm in a situation where I'd like to write a convenient formula to help speed some things up, and one of them is turning names from a variety of formats into a single uniform format.
The names can look like one of three cases:
Smith, John
Fry, Philip J
Green, Gregory (Greg)
I'm trying to write a formula so the three cases would appear as follows:
John Smith
Philip Fry
Greg Green
I've written some of a formula, however I've gotten stuck. What I have so far is:
=IF(ISNUMBER(SEARCH("(",A7)),TEXTJOIN(" ",TRUE,MID(A7,SEARCH("(",A7)+1,SEARCH(")",A7)-SEARCH("(",A7)-1),LEFT(A7,FIND(",",A7,1)-1)),TEXTJOIN(" ",TRUE,"first",LEFT(A7,FIND(",",A7,1)-1)))
It's kind of a mess, but it's what I could get so far. This will give me the following results:
first Smith
first Fry
Greg Green
So I've got a placeholder for grabbing the first name when someone does not have a nickname in parentheses. However, I can't seem to come up with the remaining pieces of the formula in order to have grab the first name after the comma (and resulting space), but ignoring any spaces before a middle initial if it exists.
Can anyone help me out or give some pointers? Thanks!