Split Cells based on condition

[Excelnovice2]

Column A has data e.g. cat is fat xy>zzy, dog is random xyzzy, rat is a hoax.set xyz, frog is alon.23e xyz, bullseye.8 x.z
I have a look up table as below
Col D
xy>zzy
xyz
x.z

(Note: the values in Col D do not have a set number of characters, so I cant use a Right/left Formula)

I need a lookup to look for the values in Col D within Col A and return the values in Col D. Is this possible please?

All I really need is col D characters seperated from the first word/words.

 

[brian.wethington]

I am pretty sure this can be done with a right function actually, but I am no formula wiz, so I would purpose a User Defined Function, is a little VBA alright for you? It would look something like this:

Function LastWord(CE As Range, Optional delim As String = " ")
Dim A
A = Split(CE, delim)
LastWord = A(UBound(A))
End Function

If you place this function in your personal.xlsb then you will use it like this in your worksheet:

=PERSONAL.XLSB!LastWord(A1)

Where A1 is the range of the particular cell you want to find the last word; drag down formula as needed.
Placing the code in the personal.xlsb will not work if you plan for others to be able to use the formula, whether to view data or to actually use it. If you should want to do that then you can place it in a module of your workbook in which you need the function and you will use it like this:

=LastWord(A1)

Should you want to use a different delimiter then that is an option for instance if you want a comma to be the delimiter:

=LastWord(A1,",")

 

[brian.wethington]

If you are needing simply a formula option, I got this to work out in tests I performed. Perhaps it works for your needs?

=IF(ISERR(FIND(" ",A1)),"",RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

Again, drag down as needed.