Extract string right to left until second repeated special character

[UserI]

Hi,
I need to clean the information from a column to result the string after the second > from the right. Examples below.

Data: Promotion > Promotion > Promotion > Performance
Result: Promotion > Performance

Data: Transfer > Transfer > Transfer > Career progression
Result: Transfer > Career progression

Data: Request compensation change > Compensation change > Market Adjustment
Result: Compensation change > Market adjustment

Hope you can help me.
Thanks a lot!

 

[Z51]

I think this might work for you. It counts the number of ">" in your cell (e.g. A1), and then replaces the count-1 instance with a caret (^). It then finds that character and adds two (assumes there is a space after the ">" before the text string starts) and retrieves the rest of the text to the right.

=MID(SUBSTITUTE(A1,">","^",LEN(A1)-LEN(SUBSTITUTE(A1,">",""))-1),FIND("^",SUBSTITUTE(A1,">","^",LEN(A1)-LEN(SUBSTITUTE(A1,">",""))-1))+2,LEN(A1))

 

[UserI]

I think this might work for you. It counts the number of ">" in your cell (e.g. A1), and then replaces the count-1 instance with a caret (^). It then finds that character and adds two (assumes there is a space after the ">" before the text string starts) and retrieves the rest of the text to the right.

=MID(SUBSTITUTE(A1,">","^",LEN(A1)-LEN(SUBSTITUTE(A1,">",""))-1),FIND("^",SUBSTITUTE(A1,">","^",LEN(A1)-LEN(SUBSTITUTE(A1,">",""))-1))+2,LEN(A1))

Thank you so much! It works and it's really really helpful.

 

[Fluff]

Another option if you have the new functions

Fluff.xlsm
	A	B
1
2	Promotion > Promotion > Promotion > Performance	Promotion > Performance
3	Request compensation change > Compensation change > Market Adjustment	Compensation change > Market Adjustment
Main

Cell Formulas
Range	Formula
B2:B3	B2	=TEXTAFTER(A2,"> ",-2)

 

[Z51]

Wow, that is much easier. Someday I'll actually look into the new functions. They may come in handy.

 

[UserI]

Another option if you have the new functions

Fluff.xlsm
	A	B
1
2	Promotion > Promotion > Promotion > Performance	Promotion > Performance
3	Request compensation change > Compensation change > Market Adjustment	Compensation change > Market Adjustment
Main

Cell Formulas
Range	Formula
B2:B3	B2	=TEXTAFTER(A2,"> ",-2)

Looks nice and easy, but I don't have the new functions yet. Thank you for your reply!

 

[Fluff]

I don't have the new functions yet

Not a problem, you'll probably get them in January if your on the semi-annual channel.