Substrings in Excel?

[mzafar]

I have a spreadsheet with Comments field in one of the cells. Cel
includes varying length comments, my goals is to read the last commen
from this cell.

So, really I am looking for a way to match anything after the las
period "." in this cell and display it in the formated report vi
Vlookup. I experimented with Left and right fuctions, however, n
luck.

Can someone please help. So, for instance the string can be somethin
like:

Hello. This is a test. Test number one.

I would like to grab the last comment.

Similary, string can be something like.

Hello. This is a test. Test number one comes after number two. Yo
must read test number one.

Regards,
Joh

 

[Niek Otten]

=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,".","~",LEN(A1)-LEN(SUBSTITUTE(A1,".",))-1)))

 

[Dave Peterson]

How about this:

=TRIM(MID(A1,FIND("^",SUBSTITUTE(A1,".","^",LEN(A1)
-LEN(SUBSTITUTE(LEFT(A1,LEN(A1)-1),".",""))
-(RIGHT(A1,1)=".")))+1,LEN(A1)))

(all one cell)

 

[mzafar]

Thanks Dave/Niek, both solutions work great!

Regards,
John