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! |
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 |