Fix this formula that worked great until....

[TWTHOMAS]

I was given this formula to help split up names:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(awards_civic_length_of_service_!A2,", "," "),"JR"," JR"),"III"," III"),"SR"," SR")

It was meant to split Juniors and Seniors and II's and III's into separate columns.

It works prefectly! ... until I get names like John Wejrowski and Susan Usrrey

Then it splits the name like WE JROWSKI JOHN and U SRRYEY SUSAN

Any way to fix this so it only splits the suffix if it isn't in the middle of a name?

Thanks

 

[XOR LX]

Hi.

Are you sure that's the entire formula in question, or just part?

The reason I ask is that, as it stands, that formula will not do anything at all towards "splitting" "into separate columns". It will simply make various substitutions within the string in A2.

Can you clarify?

Regards

 

[TWTHOMAS]

Sorry. You're correct. It looks at Column A on a different sheet where the names are listed.

The names on that sheet are in a format like: Thomas, David L II and Jones, Sam II

It essentially reasembles the names so that these formulas work in columns B thru E.

COLUMN B =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:A2)-1)*LEN($A2))+1,LEN($A2)))
COLUMN C =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:B2)-1)*LEN($A2))+1,LEN($A2)))
COLUMN D =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:C2)-1)*LEN($A2))+1,LEN($A2)))&"."
COLUMN E =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:D2)-1)*LEN($A2))+1,LEN($A2)))

The problem is when the name is pulled from the sheet named awards_civic_length_of_service, any name that has any of those characters (SR, JR)
next to each other it splits the name those chracters.

 

[XOR LX]

Sorry - still not sure about your set-up.

If I apply that formula to a string such as:

Thomas David L II

which I presume is how:

Thomas, David L II

will look after applying your multiple SUBSTITUTE function, and then copy to the right, I get:

Thomas, David, L and I all in separate cells. Is that what you want?

What's even more confusing is that your original question seemed to concern strings with "SR" or "JR" in them, yet the two examples you've just given feature neither of those, so I'm a bit none the wiser, I'm afraid.

Regards

 

[Andrew Poulsom]

It doesn't work as required when applied to the names given in Post #1 - JOHN WEJROWSKI and SUSAN USRREY. Maybe it should be:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&A2&" ",", "," ")," JR "," JR ")," III "," III ")," SR "," SR "))

 

[TWTHOMAS]

I will try that. Sorry it's so confusing.

What I am trying to do is split various names into four Columns based on if they do or do not have a middle initial and or a suffix on thier name.
The orginal formula worked great until I ran into names that also contained the equivelant of a suffix (i.e JR or SR) within the first or last name.

When the first or last name contains those characters, the formula splits the name at those characters. I will try the formula
with the additional spaces and see if that fixes it. Thanks!

 

[XOR LX]

If that doesn't work I think you'd be best advised to forget your previous formulas and to let us have some sample data and expected results on which we can develop our own solutions, rather than trying to fix/adapt those given to you by someone else.

Regards

 

[TWTHOMAS]

Worked prefectly! Thanks.