How to extract last set of digit from text string?

[xlmaniac]

Dear All,
I do have a following sample set as follows.
I would ike to extract the last set from the string as mentioned below.
Could somebody help me with the formula which can perfom the task as desired?
Sheet1

*	A	B
1	Sample Data	Desired Result
2	AA BB CCC DDD	DDD
3	AAA B CC DDD EE	EE
4	AA BBB C DDDD E FFF GG	GG
5	AAAA BB CCC DDDD EE FFFF	FFFF

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 198px;"><col style="width: 93px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[Paella1]

In B2:
=RIGHT(A2,LEN(A2)-SEARCH("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

 

[xlmaniac]

Dear Paol,
Thank you so much for the mesmerising solution.
Really appreciate your support & help

 

[Rick Rothstein]

Another way...


=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",300)),300))

 

[Paella1]

Very nice Rick.