Need advice, formula problem - separate the last word in sentence ?

[zeko90]

Hello !

I am beginner in excel formula. i please need some advice for this:

=IF(FIND("-";A1;1);SUBSTITUTE(A1;"-";" ";1)*AND(IF(ISERR(FIND(" ";A1));"";RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;" ";"*";LEN(A1)-LEN(SUBSTITUTE(A1;" ";"")))))));"no")

If i have a sentence in cell i want the last word of this sentence, but it may happen that last and penultimate word are conect with "-"(dash).

On the net i found this formulas:
=IF(ISERR(FIND(" ";A9));"";RIGHT(A9;LEN(A9)-FIND("*";SUBSTITUTE(A9;" ";"*";LEN(A9)-LEN(SUBSTITUTE(A9;" ";""))))))
and
=TRIM(RIGHT(SUBSTITUTE(A7;" ";REPT(" ";LEN(A7)));LEN(A7)))
for split or separated last word in sentence from other text

then i found these:
=SUBSTITUTE(A15; "-"; " "; 1)
i need these because it cases when the last two words associated with "-"(dash) so I have to get rid of if I want the last word

and:
=IF(FIND("-";A15;1);"yes";"no")
that found me "-"(dash). i don't know if i need these

now i want all of these formula fusion together?

any ideas ?

Thank for help realy !
Have nice day !

[Richard Buttrey]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.



As you are new to the forum I'll change it on this occasion but please note for the future.

Please also give some examples of your data and the results you expect to see. Be sure to show all possible permutations.

[daddylonglegs]

Does this work?

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"-"," ")," ",REPT(" ",99)),99))

[zeko90]

Thank you very very much, yes it's work great !