Help on the formula

[Chief Ace]

I attach a sample of what I am trying to accomplish.

On the spreadsheet that I am working on has more than 2000 rows of names but what I am having a problem with is the names with "JR" or "III" at the end of their last name. I tried to put the underscore between the last name and the "JR" or "III" but to no avail. As you can see in Row 2 I got the last name but the first name it shows "JR" vice "JOHN" but in Row 3 I got it right and so forth. So my questions are:

Is there a way to put underscore between last name and "JR" or "III"?
If there is. is it going to affect the other names without those "JR" or "III"?

I greatly appreciate any help on this matter.

v/r
Chief Ace

 

[NARAYANK991]

Hi ,

See if this works.

Narayan

 

[Chief Ace]

Hi ,

See if this works.

Narayan

NARAYANK991

Thank you it works to perfection. As always greatly appreciate the help.

Regards.
Chief Ace