Extract Text

[zinah]

Hi,

Anyone can help me with the right formula to extract the last text from the right. In below example, I need to extract whatever text comes after ABCD 1 or ABCD2.

Book2
	A	B
1	TEXT	Expected Result
2	012 - USA - ABCD 2 Green	Green
3	011 - USA - ABCD 1 Red	Red
4	013 - USA - ABCD 2 Yellow	Yellow
5	014 - USA - ABCD 2 Green	Green
6	015 - USA - ABCD 1 Red	Red
7	016 - USA - ABCD 2 Yellow	Yellow
8	017 - USA - ABCD 2 Green	Green
Sheet1

 

[Fluff]

If you just want the last word, how about

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

 

[zinah]

If you just want the last word, how about

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

As usual THANK YOU so much, that worked perfectly great.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[AhoyNC]

Another option with Excel 365 you could use FLASH FILL.
Type Green in cell B2.
Then go to DATA on ribbon
Then click on FLASH FILL and it will fill in the last word.

 

[zinah]

Another option with Excel 365 you could use FLASH FILL.
Type Green in cell B2.
Then go to DATA on ribbon
Then click on FLASH FILL and it will fill in the last word.

That would work too, thank you for your help