Find specific text at end of string

**VegasL** · 11-05-2009, 06:52 PM

Hello,

I have a list of words

dougsr
dougjr
dougII
dougIII
douglastname
dougname
etc.

I want to search the list, and whenever it finds "sr","jr","II", or "III" at the end of the words in the list, to return:

doug sr
doug jr
doug II
doug III

else put a blank.

How can this be done?

Thanks

**shg** · 11-05-2009, 07:07 PM

Not the most extensible approach:

=IF(AND(RIGHT(A1, {2,2,2,3}) <> {"SR","JR","II","III"}), A1, IF(RIGHT(A1,3)="III", LEFT(A1, LEN(A1)-3) & " III", LEFT(A1, LEN(A1)-2) & " " & RIGHT(A1, 2) ) )

**daddylonglegs** · 11-05-2009, 07:25 PM

Another option, perhaps

=REPLACE(A1,LEN(A1)-LOOKUP(2,1/(RIGHT(A1,LEN({"","II","sr","jr","III"}))={"","II","sr","jr","III"}),LEN({"","II","sr","jr","III"}))+1,0," ")