Excel function to return characters before second occurrence of a specified character

Question

I want a function to return the first two names in a cell for example

"Jones, Anna Mary" will return "Jones Anna"

I tried =LEFT(A1, FIND(" ",A1)-1) but it returns only Jones.

Thanks

Answer 1

You could get everything before the 2nd space by using this version

=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)

That would still include the comma so if you don't want that you can add a SUBSTITUTE function to remove commas, i.e.

=SUBSTITUTE(LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1),",","")

Answer 2

=LEFT(SUBSTITUTE(A1," ","-",2),FIND("-",SUBSTITUTE(A1," ","-",2),1)-1)

I used the SUBSTITUTE() function to replace the second space with a hyphen. I then used the FIND() function on the SUBSTITUTE string.