I am using excel 2007. Any way to do this in 2007? The morefunc is for upto
2003.

Thanks



"Ron Rosenfeld" wrote in message
...
On Wed, 16 May 2007 18:20:16 -0700, "Bruce"
wrote:

Given a string similar to the one listed below (this is actually testing
the
driving direction - printer friendly version pasted into one cell column
BC)

Total Est. Time: 1 hour, 35 minutes Total Est. Distance: 94.75 miles

I want to get the time needed to travel in a format that can be added or
subtracted from a time in the format of "7:00PM" (Column D)

I have been using the following (column BE)
=IF(ISERROR(VALUE(MID(BC33,SEARCH("Total Est.
Time:",BC33)+16,SEARCH("Total
Est. Distance:",BC33)-(SEARCH("Total Est.
Time:",BC33)+24)))),"",VALUE(MID(BC33,SEARCH("To tal Est.
Time:",BC33)+16,SEARCH("Total Est. Distance:",BC33)-(SEARCH("Total Est.
Time:",BC33)+24))))


and then =D32 -TIME(0,BE32,0) (this is in Column T )

This seems fine, until it is over 59 minutes. The issue is in the BE
column
test. In this case, it returns an empty value.

Thanks!


Excel stores times and dates in days and fractions of a day. It would be
easiest if you used the same method.

You want to ensure that your 7:00PM is an "Excel Time", so it should be
entered
as 7:00 PM (note the space before the P). You can also enter it as 7 p
or
other variations; but you can't enter it as 7:00PM.

You could format it to DISPLAY without the <space but you still have to
enter
it in one of the ways I showed.

In the above text string, you need to generate a value equal to

1/24 + 35/1440

How you extract the "1" and the "35" depends on the variations in your
text
string. You can certainly use a combination of FIND, MID, etc.

One simple way is to download and install Longre's free morefunc.xll
add-in
from http://xcell05.free.fr

Then, assuming that the hours are followed by the word "hour"; and minutes
by
the word "minu", you can use the following:

Hours: =REGEX.MID(A1,"\d+(?=\s*hour)")
Minutes:=REGEX.MID(A1,"\d+(?=\s*minu)")

To convert it into a value that you can add/subtract from an Excel Time:

=REGEX.MID(A1,"\d+(?=\s*hour)")/24+
REGEX.MID(A1,"\d+(?=\s*min)")/1440

HTH
--ron