Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing a string for either "Minutes" and/or hours and extracting the values
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("Tota l 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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing a string for either "Minutes" and/or hours and extracting the values
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("Tot al 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing a string for either "Minutes" and/or hours and extracting the values
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing a string for either "Minutes" and/or hours and extracting the values
On Wed, 16 May 2007 22:59:46 -0700, "Bruce" wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing a string for either "Minutes" and/or hours and extracting the values
Thank you very much Ron! It works like a champ!
"Ron Rosenfeld" wrote in message ... On Wed, 16 May 2007 22:59:46 -0700, "Bruce" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
testing a string for either "Minutes" and/or hours and extracting the values
On Fri, 18 May 2007 10:47:49 -0700, "Bruce" wrote:
Thank you very much Ron! It works like a champ! Glad to hear that! Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(C4,"~?#","") will this work to remove multiple string | Excel Worksheet Functions | |||
need a formula to find "*" in s string and multiply by preceding andfollowing values | Excel Discussion (Misc queries) | |||
Converting "uppercase" string data to "lower case" in CSV file | Excel Discussion (Misc queries) | |||
text string: "91E10" in csv file auto converts to: "9.10E+11" | Excel Discussion (Misc queries) | |||
Setting values for "TRUE","FALSE" and "#REF!" | Excel Programming |