What's wrong with this formula?

[TWTHOMAS]

I have a personnel data sheet that contains names of employees in column C of sheet named awards_civic_length_of_service.

Usually like - Jones, Bob E JR

Due to inconsistencies of spaces during input I use this formula in A2 of sheet 2 to bring the names into my data.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(awards_civic_length_of_service_!C2,", "," "),"JR"," JR"),"II"," II"),"III"," III"),"SR"," SR")

I then extract the first name, middle initial, last name and suffix into columns B, C, D, E of sheet 2

It works great with one exception and I cannot figure out why.

Column D of sheet 2 contains this formula.

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),300,100))

But Column D will not show my JR's and SR's and II's and III's unless I go to the original awards_civic_length_of_service and rid myself of ALL spaces between the middle initial and the suffix.

So the name looks like - Jones, Bob EJR

Any idea why I have to remove those spaces completely?

 

[cyrilbrd]

Hi, just curious, in the sample you provided, 'Jones, Bob E JR' each string is separated by one (1) space. Are you referring to instances where the data is encoded or imported (from the web) with additional space?
Is your intention to remove all unnecessary space while retaining one (1) space to separate each string?

 

[cyrilbrd]

Such as this:

Excel 2013
	A	B	C	D	E
1	Jones, Bob E JR	Jones	Bob	E	JR
2	Jones, Bob E JR	Jones	Bob	E	JR
3	Jones, Bob E JR	Jones	Bob	E	JR
TWTHOMAS

 

[TWTHOMAS]

That is exactly what I need to do.

I need to extract the last, first, middle, and suffix (if one exists) from a mixture of names that have varying
numbers of spaces in them.

Also where some have all 4 and some just have a First and Last name.

 

[cyrilbrd]

That is exactly what I need to do.

I need to extract the last, first, middle, and suffix (if one exists) from a mixture of names that have varying
numbers of spaces in them.

Also where some have all 4 and some just have a First and Last name.

Try this:

Excel 2013
	A	B	C	D	E
1	Jones, Bob E JR	Jones	Bob	E	JR
2	Jones, Bob E JR	Jones	Bob	E	JR
3	Jones, Bob E JR	Jones	Bob	E	JR
4	, ,Jones , Bob, ,,, , , E, JR,,, ,	Jones	Bob	E	JR
TWTHOMAS

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE($A1,","," "))," ",REPT(" ",99)),COLUMNS($B1:B1)*99-98,99))

 

[TWTHOMAS]

Fantastic! Perfect.

 

[cyrilbrd]

Fantastic! Perfect.

Most welcome, glad it worked for you.
Thanks for the feedback.