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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,362
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top