Need help adjusting this formula for erratic data

[TWTHOMAS]

I have a database of personnel and as you can imagine there are thousands of variations of names.

I use this formula on Sheet 2 to extract the name data into a cleaner version so I can split the names into 5 columns of individual data.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet 1_!C2,", "," ")," JR","JR"),"II"," II"),"III"," III")," SR","SR")

So for example I have these names in Column A of Sheet 1.

Jones, Karen J
Thomas, Joe E JR
Ecker, Sue
Farris, Tom II
Lamen, Bob J SR
Jameson, Eric SR

There are sometimes one and sometimes 2 spaces after each suffix. The first last and MI always have 1 space and a comma if there is a middle name.

So the formula above works great unless I have a name that contain 3 spaces or 1 space where the formula only has 2.

So what I need help with is adjusting this formula for the happenstance that there is a suffix that has 1, 2, or 3 space preceding it. I'm guessing I need to add more

(SUBSTITUTE

followed by more variations of

," SR","SR")

for example but wanted to make sure there isn't some kind of catch-all wildcard that will do the same thing.

Thanks

 

[ParamRay]

.
.

Have a look at Excel's TRIM function. It removes all spaces from text except for single spaces between words. Perhaps you can integrate it into your formula and simplify...

TRIM function - Excel