How can I extract name initials

Bdiwest

How can I get name initials from a column containing full name. For instance John Doe to return JD in another column. Thank you in advance!

Ilond

Paul Newcome

Make sure you change [Name Column] to whatever the appropriate column name is in your sheet.

Paul Newcome

Will it always be first and last or could there possibly be a middle name/initial? What about suffixes such as Jr or II?

Bdiwest

It's a mix of First, last and sometime first, last, Middle initial. Such as:

John Doe or John Doe H.

In both case, I still wish for it to return JD.

Thank you,

Ilond

Paul Newcome

Ok. And no suffixes?

Bdiwest

No suffixes in most cases. Thank you for looking into this.

Paul Newcome

Try this...

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

Bdiwest

That didn't do. It says #UNPARSEABLE

Paul Newcome

Make sure you change [Name Column] to whatever the appropriate column name is in your sheet.

Bdiwest

You made my day Paul. It worked!

Thank you

Paul Newcome

Happy to help. 👍️

DMaloney

Paul's formula doesn't account for a middle name and last name. I wrote this formula but don't know why it is repeating the first set of initials rather than pulling the last ones. Any ideas?

=LEFT([Participant Name]@row, 2) + " " + IF(ISNUMBER(FIND(" ", [Participant Name]@row)), MID([Participant Name]@row, FIND(" ", [Participant Name]@row) + 1, 2), "") + " " + IF(ISNUMBER(FIND(" ", [Participant Name]@row, FIND(" ", [Participant Name]@row) + 1)), MID([Participant Name]@row, FIND(" ", [Participant Name]@row, FIND(" ", [Participant Name]@row) + 1) + 1, 2), "")

Paul Newcome

@DMaloney My formula does account for a middle and last name when it is in the format specified by the poster:

John Doe or John Doe H.

If you have a different format then you would need a different formula. What exactly are you trying to output and how exactly is your data structured?

DMaloney

Sorry, @Paul Newcome , when I tried your formula within my structure it didn't produce the output I was looking for.

The input is full names: John Doe, or John Apple Doe. The output I want is to take the first two letters of all names present, so it looks like - Jo Do, or Jo Ap Do.

With the formula I wrote above, I'm getting - Jo Do Jo, or Jo Ap Jo. It's repeating the first set of initials at the end where it should be grabbing the last name initials.

Paul Newcome

@DMaloney Give this a go:

=LEFT([Name Column]@row, 2) + " " + MID([Name Column]@row, FIND(" ", [Name Column]@row) + 1, 2) + IFERROR(" " + MID([Name Column]@row, FIND(" ", [Name Column]@row, FIND(" ", [Name Column]@row) + 1) + 1, 2), "")

DMaloney

Thanks! That formula is still repeating the first set of initials: Jo Ap Jo. That occurs when the name just has a first and last name. It works when there is a first, middle, and last name.

Paul Newcome

@DMaloney That's odd. The MID should be throwing an error when it is just First Last because the FIND outputs a zero which is an invalid starting value. Wrapping that in the IFERROR to output the blank should have taken care of that instead of duplicating the first initials.

=LEFT([Name Column]@row, 2) + " " + MID([Name Column]@row, FIND(" ", [Name Column]@row) + 1, 2) + IFERROR(" " + MID([Name Column]@row, FIND(" ", [Name Column]@row, FIND(" ", [Name Column]@row) + 1) + 1, 2), "")

I put it in my own sheet to test, and sure enough the MID was throwing an error, but when I put it all together I ended up with the same issue you are running into.

Here is a modification that is working in my sheet now:

=IF(LEN([Name Column]@row) - LEN(SUBSTITUTE([Name Column]@row, " ", "")) = 1, LEFT([Name Column]@row, 2) + " " + MID([Name Column]@row, FIND(" ", [Name Column]@row) + 1, 2), LEFT([Name Column]@row, 2) + " " + MID([Name Column]@row, FIND(" ", [Name Column]@row) + 1, 2) + IFERROR(" " + MID([Name Column]@row, FIND(" ", [Name Column]@row, FIND(" ", [Name Column]@row) + 1) + 1, 2), ""))

It basically says that if there is a single space then just run the first two portions, otherwise do all three.