Substrings in Excel?


M

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
 
Ad

Advertisements

N

Niek Otten

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

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)
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top