Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extracting Numbers from string
I am trying to extract numbers from a string(s). The challenge for me is the string is never constant so the RIGHT or LEFT functions will not work for every situation. Here is an example of the string(s):
9m 2d 17h 35m 6h 19m 12h 21m 28m For the LEFT/RIGHT functions to work I need the formula to return this: 0d 00h 09m 2d 17h 35m 0d 06h 19m 0d 12h 21m 0d 00h 28m My desired end result is this so I can add the time to NOW() and know exactly what day/time a deadline ends: :9 2:17:35 (I multiply the day number by 24 to get hours for time addition) 6:19 12:21 :28 Thanks in advance for the help, Keyrookie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Numbers from string
This is really ugly but it works on the format types listed below.
2d 17h 35m 17h 35m 2d 17h 25m All on one line: =IF(COUNT(SEARCH({"d","h","m"},A1))=3,LEFT(A1,FIND ("d",A1)-1) +SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(" ",A1) +1,20),"h ",":"),"m",""),IF(COUNT(SEARCH({"h","m"},A1))= 2, --SUBSTITUTE(SUBSTITUTE(A1,"h ",":"),"m",""), IF(COUNT(FIND("d",A1)),--SUBSTITUTE(A1,"d",""), IF(COUNT(FIND("h",A1)),TIME(SUBSTITUTE(A1,"h",""), ,), TIME(0,SUBSTITUTE(A1,"m",""),))))) Format the result as CUSTOM d:hh:mm Based on your samples returns: 0:00:09 2:17:35 0:06:19 0:12:21 0:00:28 A regular expressions solution would be much shorter but I don't know enough about it to offer a solution. -- Biff Microsoft Excel MVP "Keyrookie" wrote in message ... I am trying to extract numbers from a string(s). The challenge for me is the string is never constant so the RIGHT or LEFT functions will not work for every situation. Here is an example of the string(s): 9m 2d 17h 35m 6h 19m 12h 21m 28m For the LEFT/RIGHT functions to work I need the formula to return this: 0d 00h 09m 2d 17h 35m 0d 06h 19m 0d 12h 21m 0d 00h 28m My desired end result is this so I can add the time to NOW() and know exactly what day/time a deadline ends: :9 2:17:35 (I multiply the day number by 24 to get hours for time addition) 6:19 12:21 :28 Thanks in advance for the help, Keyrookie -- Keyrookie |
#3
|
|||
|
|||
Biff,
The formula worked great except for when the cell only had XXh XXm. When there was anything to the left of XXh it returned a #FASLE! error. Example: 17h 35m (worked fine) 2d (worked fine) 2d 17h 35m (returned error) Thanks for your help, Keyrookie Quote:
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Numbers from string
On Wed, 3 Oct 2007 22:37:37 +0100, Keyrookie
wrote: I am trying to extract numbers from a string(s). The challenge for me is the string is never constant so the RIGHT or LEFT functions will not work for every situation. Here is an example of the string(s): 9m 2d 17h 35m 6h 19m 12h 21m 28m For the LEFT/RIGHT functions to work I need the formula to return this: 0d 00h 09m 2d 17h 35m 0d 06h 19m 0d 12h 21m 0d 00h 28m My desired end result is this so I can add the time to NOW() and know exactly what day/time a deadline ends: :9 2:17:35 (I multiply the day number by 24 to get hours for time addition) 6:19 12:21 :28 Thanks in advance for the help, Keyrookie Here is a UDF that will extract the days, hours and minutes from your string. The result is a number that can be added to NOW() to give you your deadline ending. (If you custom format this numerical output, you get the format you describe above, but that is not necessary if you just want to add it to NOW()). With your data, and with NOW = 10/4/2007 17:56 I get the following results from the formula: =NOW() + reExtrTime(cell_ref) 10/4/2007 18:05 10/7/2007 11:31 10/5/2007 0:15 10/5/2007 6:17 10/4/2007 18:24 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. Let me know if this works for you. ============================================== Option Explicit Function reExtrTime(str) As Double Dim re As Object Dim mc As Object Const sPatternD As String = "[\-+]?\d*\.?\d+(?=d)" Const sPatternH As String = "[\-+]?\d*\.?\d+(?=h)" Const sPatternM As String = "[\-+]?\d*\.?\d+(?=m)" Set re = CreateObject("vbscript.regexp") re.Global = True re.ignorecase = True re.Pattern = sPatternD If re.test(str) Then Set mc = re.Execute(str) reExtrTime = mc(0) End If re.Pattern = sPatternH If re.test(str) Then Set mc = re.Execute(str) reExtrTime = reExtrTime + mc(0) / 24 End If re.Pattern = sPatternM If re.test(str) Then Set mc = re.Execute(str) reExtrTime = reExtrTime + mc(0) / 1440 End If End Function ============================= --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Numbers from string
It worked ok for me on all the samples you posted.
Try Ron's UDF. That formula is real ugly! -- Biff Microsoft Excel MVP "Keyrookie" wrote in message ... Biff, The formula worked great except for when the cell only had XXh XXm. When there was anything to the left of XXh it returned a #FASLE! error. Example: 17h 35m (worked fine) 2d (worked fine) 2d 17h 35m (returned error) Thanks for your help, Keyrookie T. Valko;565313 Wrote: This is really ugly but it works on the format types listed below. 2d 17h 35m 17h 35m 2d 17h 25m All on one line: =IF(COUNT(SEARCH({"d","h","m"},A1))=3,LEFT(A1,FIND ("d",A1)-1) +SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(" ",A1) +1,20),"h ",":"),"m",""),IF(COUNT(SEARCH({"h","m"},A1))= 2, --SUBSTITUTE(SUBSTITUTE(A1,"h ",":"),"m",""), IF(COUNT(FIND("d",A1)),--SUBSTITUTE(A1,"d",""), IF(COUNT(FIND("h",A1)),TIME(SUBSTITUTE(A1,"h",""), ,), TIME(0,SUBSTITUTE(A1,"m",""),))))) Format the result as CUSTOM d:hh:mm Based on your samples returns: 0:00:09 2:17:35 0:06:19 0:12:21 0:00:28 A regular expressions solution would be much shorter but I don't know enough about it to offer a solution. -- Biff Microsoft Excel MVP "Keyrookie" wrote in message ...- I am trying to extract numbers from a string(s). The challenge for me is the string is never constant so the RIGHT or LEFT functions will not work for every situation. Here is an example of the string(s): 9m 2d 17h 35m 6h 19m 12h 21m 28m For the LEFT/RIGHT functions to work I need the formula to return this: 0d 00h 09m 2d 17h 35m 0d 06h 19m 0d 12h 21m 0d 00h 28m My desired end result is this so I can add the time to NOW() and know exactly what day/time a deadline ends: :9 2:17:35 (I multiply the day number by 24 to get hours for time addition) 6:19 12:21 :28 Thanks in advance for the help, Keyrookie -- Keyrookie - -- Keyrookie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Numbers froma Text String | Excel Worksheet Functions | |||
Extracting a numbers from a text string | Excel Worksheet Functions | |||
Extracting numbers from string of text | Excel Discussion (Misc queries) | |||
extracting numbers within text string! | Excel Worksheet Functions | |||
extracting numbers from string | Excel Discussion (Misc queries) |