Formula needed for extracting a three part string

[Cmadd18]

HI Everyone, I would like some help with a formula please. I have a three part name and need to extract the first letter of the third name. An example would be in cell A1 I have the name John Joseph Smith. I need to extract the S from Smith into the cell B1. I have multiple three part names. What would you suggest? Thank you.

 

[t0azt]

Assuming the name is in A1 you can try:

=MID(A1,FIND(" ",SUBSTITUTE(A1," ","-",1))+1,1)

 

[Cmadd18]

Hi, Thanks. This did work perfectly. Have a great evening.

 

[Cmadd18]

Hi Again, What if the name has four and five names. An example would be John J. Bean Smith and I want to extra the S from S? The five word string would be John Joseph De La Smith. I still want to extract the S from Smith. Thanks

 

[pkdeimos]

Can you try this?

=MID(A1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,1)

 

[Rick Rothstein]

Here is another formula for you to try...

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))

 

[Cmadd18]

Can you try this?

=MID(A1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,1)

Thank you this works also. Have a good day

 

[Cmadd18]

Hi Rick, Thank you for your formula it work great. It's nice to have this options and this is a time saver. Have a great day.