# How can I extract name initials

Options
✭✭✭

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

• ✭✭✭✭✭✭
Options

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

«1

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Ok. And no suffixes?

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Try this...

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

• ✭✭✭
Options

That didn't do. It says #UNPARSEABLE

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

You made my day Paul. It worked!

Thank you

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭
edited 09/23/22
Options

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), "")

• ✭✭✭✭✭✭
Options

@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?

• ✭✭
edited 09/26/22
Options

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.

• ✭✭✭✭✭✭
Options

@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), "")

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

@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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!