Formula to extract a portion of a name

Khari Shiver

Hello,,

In Excel I would use text to columns to do what I'm trying to accomplish here but I would like to try a different approach. I'm really close but seem to have hit an impasse.

I have a name column that is formatted thusly Lastname, Firstname MI. where some employees do not have a middle initial. I want a column with the first name only and while I could do a vlookup against another sheet I want to solve it using a formula.

Employee Name contains the "Lastname, Firstname, MI. and the formula I have is:

=RIGHT([Employee Name]@row, LEN([Employee Name]@row) - FIND(", ", [Employee Name]@row))

It gives me Firstname MI so "Smith, John A." shows up as "John A." I'm trying to just have "John."

Thank you,

Khari

Nick Korna

Hi @Khari Shiver,

This should work for you, I think:

=IFERROR(MID([Employee Name]@row, (FIND(" ", [Employee Name]@row, (FIND(", ", [Employee Name]@row))) + 1), (FIND(" ", [Employee Name]@row, (FIND(", ", [Employee Name]@row) + 2)) - FIND(" ", [Employee Name]@row, (FIND(", ", [Employee Name]@row))) - 1)), RIGHT([Employee Name]@row, LEN([Employee Name]@row) - FIND(", ", [Employee Name]@row)))

This section finds the first name if there is an middle initial present:

(MID([Employee Name]@row, (FIND(" ", [Employee Name]@row, (FIND(", ", [Employee Name]@row))) + 1), (FIND(" ", [Employee Name]@row, (FIND(", ", [Employee Name]@row) + 2)) - FIND(" ", [Employee Name]@row, (FIND(", ", [Employee Name]@row))) - 1))

If there is not a middle initial then this would return an error, so the IFERROR and your original formula then go to work and capture the rightmost portion which should be the first name.

Some sample results:

The only failing of this if the comma between lastname & firstname is absent you will get a result showing both.

Hope this helps, if you've any questions then just ask! 😊

Nick Korna

Hi @Khari Shiver,

This should work for you, I think:

=IFERROR(MID([Employee Name]@row, (FIND(" ", [Employee Name]@row, (FIND(", ", [Employee Name]@row))) + 1), (FIND(" ", [Employee Name]@row, (FIND(", ", [Employee Name]@row) + 2)) - FIND(" ", [Employee Name]@row, (FIND(", ", [Employee Name]@row))) - 1)), RIGHT([Employee Name]@row, LEN([Employee Name]@row) - FIND(", ", [Employee Name]@row)))

This section finds the first name if there is an middle initial present:

(MID([Employee Name]@row, (FIND(" ", [Employee Name]@row, (FIND(", ", [Employee Name]@row))) + 1), (FIND(" ", [Employee Name]@row, (FIND(", ", [Employee Name]@row) + 2)) - FIND(" ", [Employee Name]@row, (FIND(", ", [Employee Name]@row))) - 1))

If there is not a middle initial then this would return an error, so the IFERROR and your original formula then go to work and capture the rightmost portion which should be the first name.

Some sample results:

The only failing of this if the comma between lastname & firstname is absent you will get a result showing both.

Hope this helps, if you've any questions then just ask! 😊

Khari Shiver

Thank you for this! It works and I'm going to take some time to study what you've done.

Khari