Extracting two words from a string

Hello,

I was wondering if someone can assist.

I have a string in the format:

0000000000 Text Text Text, 2008 Text Text-Text-Text for FirstName LastName #000000

I need a formula to extract the First Name and Last Name from the string to another column without any white spaces inbetween.

So far, I have come up with: =REPLACE(A1,1,FIND("for",A1)+3,"") which gives me the following result: FirstName LastName #000000

I need to get rid of the last bit, '#000000' and remove the white-space in-between FirstName and LastName.

Thank you.

Try

=TRIM(REPLACE(REPLACE(A1,FIND("#",A1),LEN(A1),""),1,FIND("for",A1)+3,""))

Thanks guys for the quick reply :D

I can now get the FirstName and LastName.

Is there a way to tweak it so there is no whitespace between FirstName and LastName?

Perhaps

=SUBSTITUTE(REPLACE(REPLACE(A1,FIND("#",A1),LEN(A1),""),1,FIND("for",A1)+3,"")," ","")

Yeay! Thank you very much everyone!

Just was I was looking for.

Saved me from loads of stress today.

Great, glad to help.