Extract multiple uppercase text from within a string

[whicks64]

Hi,

I have list of addresses that have address and suburb combined into one cell. The suburbs are all uppercase and I'm trying to find a formula to extract these to another cell.

Examples (located in column B of my spreadsheet)
Cnr Hollywood Drive and Wharf Road LANSVALE
Cnr Hamilton Drive & Tongarra Road ALBION PARK
2A Hillcrest Avenue BARDWELL VALLEY

I can use the following array formula found in another forum to extract suburbs with a single name but can't get the suburbs that consist of two names

=RIGHT(B2,MATCH(" ",MID(B2,LEN(B2)-ROW(INDIRECT("1:"&LEN(B2))),1),0))

any assistance appreciated

 

[Ganjin]

=RIGHT(B2,MATCH(0,--EXACT(UPPER(MID(B2,LEN(B2)-ROW(INDIRECT("1:"&LEN(B2))),1)),MID(B2,LEN(B2)-ROW(INDIRECT("1:"&LEN(B2))),1)),0)-1)
Ctrl+Shift+Enter

 

[whicks64]

=RIGHT(B2,MATCH(0,--EXACT(UPPER(MID(B2,LEN(B2)-ROW(INDIRECT("1:"&LEN(B2))),1)),MID(B2,LEN(B2)-ROW(INDIRECT("1:"&LEN(B2))),1)),0)-1)
Ctrl+Shift+Enter

Thanks Ganjin,

That works perfectly!