Fix this formula that worked great until....

TWTHOMAS

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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,279
Members
430,201
Latest member
Deepakpilla36

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
Top