testing a string for either "Minutes" and/or hours and extracting the values

[Bruce]

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("Total 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!

 

[Ron Rosenfeld]

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("Total 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

 

[Bruce]

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

Thanks

 

[Ron Rosenfeld]

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

Thanks

Hmmm. I was not aware that morefunc would not work in Excel 2007. Did you try
it?

If it doesn't work, then I would use a UDF, using the same principal of regular
expressions, which should work, although having XL2003, I cannot test it in
XL2007.

To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter the function =GetTime(str) into some cell, where str is
either the actual string, OR a cell reference to a cell that contains the
string.

It will return the Excel equivalent of the appropriate time (or a zero if there
is no time, defined as before, in the string).

If the times are expressed as other than integers, or if they are defined by
other than a number being followed by the substring "hour" or "minu", then the
regex Pattern will need to be modified.

If you want to add "seconds", you'd need to add an appropriate pattern and then
extract it similarly to the hours and minutes, except you would divide it by
86,400 (24*60*60) instead of the 24 or 1440.

=====================================================
Option Explicit

Function GetTime(str As String) As Double
Dim oRegex As Object
Dim oMatchCollection As Object
Const sPatternH As String = "\d+(?=\s*hour)"
Const sPatternM As String = "\d+(?=\s*minu)"
Set oRegex = CreateObject("VBScript.RegExp")

With oRegex
.IgnoreCase = False
.Global = True
End With

'get hours
With oRegex
.Pattern = sPatternH
If .test(str) = True Then
Set oMatchCollection = .Execute(str)
GetTime = oMatchCollection(0) / 24
End If
'get minutes
.Pattern = sPatternM
If .test(str) = True Then
Set oMatchCollection = .Execute(str)
GetTime = GetTime + oMatchCollection(0) / 1440
End If
End With
End Function
=============================================

The above can be done with a complex formula, but you would want to also
account for errors, and that would add even more complexity, as well as make
the result difficult to modify and/or debug.
--ron

 

[Bruce]

Thank you very much Ron! It works like a champ!

Hmmm. I was not aware that morefunc would not work in Excel 2007. Did
you try
it?

If it doesn't work, then I would use a UDF, using the same principal of
regular
expressions, which should work, although having XL2003, I cannot test it
in
XL2007.

To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste
the
code below into the window that opens.

To use this, enter the function =GetTime(str) into some cell, where str is
either the actual string, OR a cell reference to a cell that contains the
string.

It will return the Excel equivalent of the appropriate time (or a zero if
there
is no time, defined as before, in the string).

If the times are expressed as other than integers, or if they are defined
by
other than a number being followed by the substring "hour" or "minu", then
the
regex Pattern will need to be modified.

If you want to add "seconds", you'd need to add an appropriate pattern and
then
extract it similarly to the hours and minutes, except you would divide it
by
86,400 (24*60*60) instead of the 24 or 1440.

=====================================================
Option Explicit

Function GetTime(str As String) As Double
Dim oRegex As Object
Dim oMatchCollection As Object
Const sPatternH As String = "\d+(?=\s*hour)"
Const sPatternM As String = "\d+(?=\s*minu)"
Set oRegex = CreateObject("VBScript.RegExp")

With oRegex
.IgnoreCase = False
.Global = True
End With

'get hours
With oRegex
.Pattern = sPatternH
If .test(str) = True Then
Set oMatchCollection = .Execute(str)
GetTime = oMatchCollection(0) / 24
End If
'get minutes
.Pattern = sPatternM
If .test(str) = True Then
Set oMatchCollection = .Execute(str)
GetTime = GetTime + oMatchCollection(0) / 1440
End If
End With
End Function
=============================================

The above can be done with a complex formula, but you would want to also
account for errors, and that would add even more complexity, as well as
make
the result difficult to modify and/or debug.
--ron

 

[Ron Rosenfeld]

Thank you very much Ron! It works like a champ!

Glad to hear that! Thanks for the feedback.
--ron