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
PETER ENGLAND ENGLAND	ENGLAND
sUN/Water-Rose Prime PRIME	PRIME
Daniel Murray-8horse/55buffALo123 55BUFFALO123	55BUFFALO123
riSiNg888 RISING888	RISING888

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=UPPER(TRIM(RIGHT(SUBSTITUTE(TRIM(X1)," ",REPT(" ",99)),99)))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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