Formula to extract first name for Empl Nm Column

Thi Chinatangul

Hello,

Please excuse my beginner's question.

Is there a formula to extract the first name from an Name Colum with Last, First Mid Initial format?

ie. Doe, John A

Thank you in advance for your help.

Paul Newcome

Try something like this...

=MID(Name@row, FIND(",", Name@row) + 2, FIND(" ", Name@row, FIND(",", Name@row) + 2) - (FIND(",", Name@row) + 2))

Thi Chinatangul

Thank you!  and what if there's no Middle initial?

Paul Newcome

Then we would use something along the lines of...

=RIGHT(Name@row, LEN(Name@row) - (FIND(" ", Name@row) + 1))

To put both into a single formula so that it requires less manual intervention...

=IF(FIND(" ", Name@row, LEN(Name@row) - 3) > 0, MID(Name@row, FIND(",", Name@row) + 2, FIND(" ", Name@row, FIND(",", Name@row) + 2) - (FIND(",", Name@row) + 2)), RIGHT(Name@row, LEN(Name@row) - (FIND(" ", Name@row) + 1)))

Paula_

@Paul Newcome

I am trying to use this in order to pull a first name from a column that is in a Last, First, (and sometimes Middle Initial) and its not quite right. I believe it's because the source data I have coming in doesn't have a space after the comma from Last,First MI because it does work when I entered a space in.

Can you please help me adjust for this in the formula?

=IF(FIND(" ", [Lead Producer]@row, LEN([Lead Producer]@row) - 3) > 0, MID([Lead Producer]@row, FIND(",", [Lead Producer]@row) + 2, FIND(" ", [Lead Producer]@row, FIND(",", [Lead Producer]@row) + 2) - (FIND(",", [Lead Producer]@row) + 2)), RIGHT([Lead Producer]@row, LEN([Lead Producer]@row) - (FIND(" ", [Lead Producer]@row) + 1)))

Paul Newcome

@Paula_ Try this:

=MID([Lead Producer]@row, FIND(",", [Lead Producer]@row) + 1, IF(FIND(" ", [Lead Producer]@row) > 0, FIND(" ", [Lead Producer]@row, LEN([Lead Producer]@row)) - (FIND(",", [Lead Producer]@row) + 1))

Paula_

Thank you Paul - tried it and got an Invalid value message.

Paul Newcome

@Paula_ Ok. What does this one do?

=IFERROR(MID([Lead Producer]@row, FIND(",", [Lead Producer]@row) + 1, FIND(" ", [Lead Producer]@row) - (FIND(",", [Lead Producer]@row) + 1)), RIGHT([Lead Producer]@row, LEN([Lead Producer]@row) - (FIND(",", [Lead Producer]@row) + 1))))

Paula_

@Paul Newcome

I am getting an Unparseable error with that one

Paul Newcome

@Paula_ I think I may have put one too many closing parenthesis on the end. Try removing one and see fi that helps. If not, are you able to provide a screenshot of the formula open in the sheet as if you are about to edit it?

Paula_

That worked!! Just removed the end one and it's working, except when there isn't a middle initial. Sadly those aren't included in all the names, its a mixture.

Paul Newcome

What are you getting when there isn't a middle initial?

Paula_

Oh apologies - that would be a helpful thing to share. it cuts off the first letter of the first name.

Paul Newcome

Give this a try then:

=IFERROR(MID([Lead Producer]@row, FIND(",", [Lead Producer]@row) + 1, FIND(" ", [Lead Producer]@row) - (FIND(",", [Lead Producer]@row) + 1)), RIGHT([Lead Producer]@row, LEN([Lead Producer]@row) - FIND(",", [Lead Producer]@row))))

Paula_

You are the best - that worked!! :) THANK YOU!

Paul Newcome

Happy to help. 👍️