0

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!

Improve this question

2 Answers 2

Reset to default
0

This first finds if any word is in () and removes everything between the , and ( then it creates a pseudo xml statement that FILTERXML passes first the second name then the first to TEXTJOIN:

=TEXTJOIN(" ",TRUE,FILTERXML("<a><b>"&SUBSTITUTE(TRIM(LEFT(A1,FIND(",",A1)-1)& " " &MID(LEFT(A1,FIND(")",A1&")")-1),IFERROR(FIND("(",A1)+1,FIND(",",A1)+1),999))," ","</b><b>")&"</b></a>","//b["&{2,1}&"]"))

Depending on ones version this may require the use of Ctrl-Shift-enter instead of Enter when exiting edit mode.

enter image description here

Improve this answer
3
  • Thanks so much! I've pasted this code in and it seems to only be showing me the first name, and no last names. Just want to make sure I'm getting this right. I'm a little unfamiliar with XML and am struggling to work my way through the code and find where I'm having an issue.
    – rocketPowered
    Commented Apr 24, 2020 at 1:42
  • Try using Ctrl-Shift-enter instead of enter when exiting edit mode.
    – Scott Craner
    Commented Apr 24, 2020 at 1:52
  • If Im editing the cell, when I press Ctrl-Shift-Enter nothing happens, it remains editing. This seems odd, and I've been googling to try and find a solution but have nothing that works so far.
    – rocketPowered
    Commented Apr 24, 2020 at 2:20
0

I had some issues with array formulas, so I've gone ahead and finished the formula I had using the substitute function to check how many spaces there are in a name. If someone has multiple last names, I solved this by checking only spaces after the comma separating the first and last names.

The working (and horribly ugly) formula is:

=IF(ISNUMBER(SEARCH("(",A1)),TEXTJOIN(" ",TRUE,MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1),LEFT(A1,FIND(",",A1,1)-1)),TEXTJOIN(" ",TRUE,IF(LEN(RIGHT(A1,LEN(A1)-FIND(",",A1)))-LEN(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(",",A1))," ",""))=1,RIGHT(A1,LEN(A1)-FIND(",",A1)-1),LEFT(RIGHT(A1,LEN(A1)-FIND(",",A1)-1),FIND(" ",RIGHT(A1,LEN(A1)-FIND(",",A1)-2)))),LEFT(A1,FIND(",",A1,1)-1)))
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .