Search last occurence of character and return word that contains it

[szerendipal]

I would like a formula that looks for a specific character in cell and returns the last word that contains it.

Example: extract the last word (string?) from A1 that contains the "." (dot) character:

A1	B1
FirstName LastName 1984 1:15.84 1:07.84 2	1:07.84

 

[Peter_SSs]

Welcome to the MrExcel board!

One example isn't much to go on.
If the word in question is always the 2nd last word like in that example, try

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200),100))

However, if the word in question can be located in different positions then try this.

20 11 16.xlsm
	A	B
1	FirstName LastName 1984 1:15.84 1:07.84 2	1:07.84
2	FirstName LastName 1984 1:15.84 1:07.84	1:07.84
3	FirstName LastName 1984 1:15.84 1:07:84 4	1:15.84
Last

Cell Formulas
Range	Formula
B1:B3	B1	=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-1)-1)," ",REPT(" ",100)),100))

 

[szerendipal]

Welcome to the MrExcel board!

One example isn't much to go on.
If the word in question is always the 2nd last word like in that example, try

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200),100))

However, if the word in question can be located in different positions then try this.

20 11 16.xlsm
	A	B
1	FirstName LastName 1984 1:15.84 1:07.84 2	1:07.84
2	FirstName LastName 1984 1:15.84 1:07.84	1:07.84
3	FirstName LastName 1984 1:15.84 1:07:84 4	1:15.84
Last

Cell Formulas
Range	Formula
B1:B3	B1	=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-1)-1)," ",REPT(" ",100)),100))

Thanks, option 2 was exactly what I was looking for!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.