easy solution formula finding first and last name

[jordanburch]

Hi All I have the below

=OR(LEFT(E176,LEN(E176)-SEARCH(" ",E176)),LEFT(E176,FIND(",",E176,1)-1)) referencing either the two names I need it to pull out the first and the last name as they can be different. E176 could look like the below. Its giving a value error when I coombine them. Just need to to work on either or the first name no matter how it shows in e176.

CAMPBELL VICTORIA DAVIS,KRISTIN

 

[FormR]

Hi, here's one way you could try.

Book1
	E	F
176	CAMPBELL VICTORIA	CAMPBELL
177	DAVIS,KRISTIN	DAVIS
Sheet1

Cell Formulas
Range	Formula
F176:F177	F176	=LEFT(E176,FIND(",",SUBSTITUTE(E176," ",",")&",")-1)

 

[Cubist]

It's unclear whether the names without the commas are first name last name or last name first name. If the former then you can try:

Book1
	A	B	C
1	Full Name	First name	Last Name
2	CAMPBELL VICTORIA	CAMPBELL	VICTORIA
3	DAVIS,KRISTIN	KRISTIN	DAVIS
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=IFERROR(MID(A2,FIND(",",A2)+1,LEN(A2)),LEFT(A2,FIND(" ",A2)-1))
C2:C3	C2	=IFERROR(LEFT(A2,FIND(",",A2)-1),MID(A2,FIND(" ",A2)+1,LEN(A2)))

 

[jordanburch]

Hi, here's one way you could try.

Book1
	E	F
176	CAMPBELL VICTORIA	CAMPBELL
177	DAVIS,KRISTIN	DAVIS
Sheet1

Cell Formulas
Range	Formula
F176:F177	F176	=LEFT(E176,FIND(",",SUBSTITUTE(E176," ",",")&",")-1)

that worked thanks! Now im trying to do the right which would be the first name

=RIGHT(E176,FIND(",",SUBSTITUTE(E176," ",",")&",")+1)

It comes out correctly on that when I do plus 1 but on otheres in includes part of the last letter of the last name for example

=RIGHT(E175,FIND(",",SUBSTITUTE(E175," ",",")&",")+1)

CAMPBELL VICTORIA I just want it to say Victoria =RIGHT(E188,FIND(",",SUBSTITUTE(E188," ",",")&",")+1) TOWNER,MARIAH gives me R,Mariah just need Mariah Can you please help with that to bro?

 

[jordanburch]

It's unclear whether the names without the commas are first name last name or last name first name. If the former then you can try:

Book1
	A	B	C
1	Full Name	First name	Last Name
2	CAMPBELL VICTORIA	CAMPBELL	VICTORIA
3	DAVIS,KRISTIN	KRISTIN	DAVIS
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=IFERROR(MID(A2,FIND(",",A2)+1,LEN(A2)),LEFT(A2,FIND(" ",A2)-1))
C2:C3	C2	=IFERROR(LEFT(A2,FIND(",",A2)-1),MID(A2,FIND(" ",A2)+1,LEN(A2)))

wow you are brilliant worked like a charm God bless!

 

[Cubist]

wow you are brilliant worked like a charm God bless!

You're welcome. Glad we can help.