Fix this formula that worked great until....

TWTHOMAS

Board Regular
Joined
Mar 26, 2010
Messages
96
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 "))
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,367
Messages
6,119,114
Members
448,869
Latest member
Ceblevey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top