Extracting Numbers from string

[Keyrookie]

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

 

[T. Valko]

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.

 

[Keyrookie]

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

 

[Ron Rosenfeld]

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

 

[T. Valko]

It worked ok for me on all the samples you posted.

Try Ron's UDF. That formula is real ugly!