Substrings in Excel?

[mzafar]

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

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

Can someone please help. So, for instance the string can be something 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. You must read test number one.

Regards,
John

[Niek Otten]

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

--
Kind regards,

Niek Otten

"mzafar" <[email protected]> wrote in
message news:[email protected]...
>
> I have a spreadsheet with Comments field in one of the cells. Cell
> includes varying length comments, my goals is to read the last comment
> from this cell.
>
> So, really I am looking for a way to match anything after the last
> period "." in this cell and display it in the formated report via
> Vlookup. I experimented with Left and right fuctions, however, no
> luck.
>
> Can someone please help. So, for instance the string can be something
> 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. You
> must read test number one.
>
> Regards,
> John
>
>
> --
> mzafar
> ------------------------------------------------------------------------
> mzafar's Profile:
> http://www.excelforum.com/member.php...o&userid=16096
> View this thread: http://www.excelforum.com/showthread...hreadid=517319
>

[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 wrote:
>
> I have a spreadsheet with Comments field in one of the cells. Cell
> includes varying length comments, my goals is to read the last comment
> from this cell.
>
> So, really I am looking for a way to match anything after the last
> period "." in this cell and display it in the formated report via
> Vlookup. I experimented with Left and right fuctions, however, no
> luck.
>
> Can someone please help. So, for instance the string can be something
> 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. You
> must read test number one.
>
> Regards,
> John
>
> --
> mzafar
> ------------------------------------------------------------------------
> mzafar's Profile: http://www.excelforum.com/member.php...o&userid=16096
> View this thread: http://www.excelforum.com/showthread...hreadid=517319

--

Dave Peterson

[mzafar]

Thanks Dave/Niek, both solutions work great!

Regards,
John