How to remove Middle Initials and Extra Space from Names : Office 365

Help with Office 365: I'm new and learning formulas so I'd appreciate any help with this as well as any explanation or tutorials that were helpful with this problem.

I can remove the "/" easily using the Find and Replace, so the concern is more how to tell excel I want to remove the last space and last character standing alone or character(s).


Ex: TheMenace/ Dennis L becomes TheMenace Dennis
or Hollywood/Jack III becomes Hollywood Jack


(with no extra spaces at the end of the name). This is tricky since the characters might vary but generally it'd be a space and one or two characters.

Thank you in advance, I have 10,000 lines to edit and this will save me so much time, I really appreciate it!

Hi Glenn,

Thank you for your help. I created a sample to play with.

Your formula works great, however in instances it has one middle initial character it removes that character from the Last and First name as well, however the error is not pressent if there are 2 initials?

(See cells B2:B4)

Thank you for your help, I really appreciate it!

Hi Glenn,

THANK YOU!!!

This is absolutely amazing, I really appreciate the help, it would have taken DAYS of hand typing and after I got to line 300 I figured I need to research and got stuck in errors with formulas. I really appreciate the help! Thank you again!