# Fix this formula that worked great until....

#### TWTHOMAS

##### Board Regular
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

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.

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

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 "))

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!

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

Worked prefectly! Thanks.

Replies
28
Views
2K
Replies
1
Views
2K
Replies
6
Views
424
Replies
1
Views
218
Replies
2
Views
300

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.

### Which adblocker are you using?

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

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