Suppose you have following string
Moscow, London, Paris, Delhi, Washington, Miami, Detroit, Berlin
And you want to retrieve nth word from front.
You can use following FILTERXML formula for this
=FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s[N]")
Where N need to be replaced with the word number which you want.
Hence, if you wanted 2nd word, then replace that with 2
=FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s[2]")
Note – In my string, comma followed by a space is separator. If your separator is different say space, replace red part in SUBSTITUTE(A2,", ","</s><s>"). Hence, if your separator is simply a space, then
=FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s[N]")
Now you want to retrieve nth word from back.
You can use following FILTERXML formula for this
=FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s["&LEN(A2)-LEN(SUBSTITUTE(A2,", "," "))+2-N&"]")
Hence, if you want last word, replace N with 1. If you need 2nd last word, replace N with 2.
Hence for 3rd last word, the formula would be (after replacing N with 3)
=FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s["&LEN(A2)-LEN(SUBSTITUTE(A2,", "," "))+2-3&"]")
Note – Here again, separator in SUBSTITUTE need to be replaced appropriately if you are not using my separator in your string.