Left, Right, Find, Formula Help!

KALESHIRE

I need help creating formulas to split cell information.

One cell contains indexed information in this string format:

FirstName LastName Email@Email 888-888-8888

I need the formula to split to each of these 3 columns

1. FirstName LastName
2. Email
3. Phone

Thank you!

Paul Newcome

Will there ever be a middle initial or middle name, or can we base it on the name stopping at the second space, email stops at the third space, and phone is everything to the right of the third space?

heyjay

Name =SUBSTITUTE(SUBSTITUTE(Table@row, Email@row, ""), Phone@row, "")

Email =MID(SUBSTITUTE(Table@row, Phone@row, ""), FIND(" ", SUBSTITUTE(Table@row, Phone@row, ""), FIND(" ", SUBSTITUTE(Table@row, Phone@row, "")) + 1) + 1, LEN(SUBSTITUTE(Table@row, Phone@row, "")) - FIND(" ", SUBSTITUTE(Table@row, Phone@row, ""), FIND(" ", SUBSTITUTE(Table@row, Phone@row, "")) + 1))

Phone =RIGHT(Table@row, 12)

Result:

KALESHIRE

@Paul Newcome No, there will never be a middle initial and will always be in this exact string format.

Paul Newcome

In that case I would suggest these formulas:

Name:

=LEFT([Column Name@row, FIND(" ", [Column Name]@row, FIND(" ", [Column Name]@row)))

Email:

=SUBSTITUTE(SUBSTITUTE([Column Name]@row, Name@row, ""), Phone@row, "")

Phone:

=RIGHT([Column Name]@row, 12)

KALESHIRE

@Paul Newcome

Thanks! It is working for the most part, except the last name is pulling to the beginning of the email column instead of the end of the name column. Is there a change in formula I can do?

Screenshot 2024-07-31 142114.jpg

Paul Newcome

Give this a shot:

=LEFT([Column Name@row, FIND(" ", [Column Name]@row, FIND(" ", [Column Name]@row)) - 1)

KALESHIRE

@Paul Newcome For some reason, it is still only returning the first name…?

Paul Newcome

That's my fault. I forgot to put a plus one in there.

=LEFT([Column Name]@row, FIND(" ", [Column Name]@row, FIND(" ", [Column Name]@row) + 1) - 1)

KALESHIRE

Added this and it shows "imparseable".

=LEFT([ar@row], FIND(" ", [ar@row], FIND(" ", [ar@row]) + 1) - 1)

Paul Newcome

Try removing the square brackets. Those go only around the column name itself, and only when there are spaces, numbers, and/or special characters in the column names.