Extract last word from a cell value

[hsandeep]

I am having 'NAMES' in column X5:X9999. NAMES contains 4 words MAXIMUM of alphanumeric characters separated by either a blank or "/" or "-".
How to get the last word from the NAMES? All output required in UPPERCASE only in column Z5:Z9999

Ex:
PETER ENGLAND ENGLAND
sUN/Water-Rose Prime PRIME
Daniel Murray-8horse/55buffALo123 55BUFFALO123
riSiNg888 RISING888

I am using Excel2007. Thanks in advance.

 

[Robert Mika]

lik this?
Excel 2010

	X	Z
1	PETER ENGLAND ENGLAND	ENGLAND
2	sUN/Water-Rose Prime PRIME	PRIME
3	Daniel Murray-8horse/55buffALo123 55BUFFALO123	55BUFFALO123
4	riSiNg888 RISING888	RISING888

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =UPPER(TRIM(RIGHT(SUBSTITUTE(TRIM(X1)," ",REPT(" ",99)),99))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[hsandeep]

It WORKS. Thanks.
Can I get FIRSTWORD + LASTWORD 'together'.
Answer for 3: DANIEL55BUFFALO123

 

[Robert Mika]

=trim(left(substitute(trim(a1)," ",rept(" ",200)),200))&trim(right(substitute(trim(a1)," ",rept(" ",200)),200))

 

[hsandeep]

It WORKS, Thanks again.
There are three cells in which 3 special characters are punched like AA1="+", AB1="/" & AC1="-".
Can these 3 special characters be included in between FIRSTNAME & LASTNAME.
Answer: DANIEL+/-55BUFFALO123
There can be blank also in AA1:AC1.

 

[Robert Mika]

=trim(left(substitute(trim(a1)," ",rept(" ",200)),200))&aa1&ab1&ac1&trim(right(substitute(trim(a1)," ",rept(" ",200)),200))

 

[ramananhrm]

Please try this.

=SUBSTITUTE(RIGHT(SUBSTITUTE(X1," ",REPT("*",LEN(X1))),LEN(X1)),"*","")

 

[ramananhrm]

=substitute(left(substitute(x1," ",rept("*",len(x1))),len(x1)),"*","")&substitute(right(substitute(x1," ",rept("*",len(x1))),len(x1)),"*","")

 

[hsandeep]

Ramananhrm,
Thanks, it works.

 

[hsandeep]

=trim(left(substitute(trim(a1)," ",rept(" ",200)),200))&aa1&ab1&ac1&trim(right(substitute(trim(a1)," ",rept(" ",200)),200))

As usual, it WORKS. Thanks