parse numeric data from alphanumeric string

B

BlackIce

I have a string in the format 01 Day(s) 08 Hour(s) 34 Minutes, how can
I parse out just the numbers so i can convert them and add them
together so i would get 24+8+0.56?

Any help appreciated, Thanks.
 
C

Chip Pearson

Try

Sub AAA()
Dim Arr As Variant
Dim S As String
Dim Ndx As Long
Dim Res As Double
S = "01 Day(s) 08 Hour(s) 34 Minutes"
Arr = Split(S, " ")
Res = (CDbl(Arr(0)) * 24) + CDbl(Arr(2)) + (CDbl(Arr(4)) / 60)
Debug.Print Res
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
B

BlackIce

What I was really looking for was an excel formula to do this, not VBA
code. So far i have taken out all the words as a result of doing
several Substitute() functions and just need to be able to do the math
part, but to do that I need to be able to parse out the numbers, do the
math and then put them back in. Thanks
 
B

BlackIce

So far i did this,

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " minutes", "", 1), " hour(s)",
"", 1), " day(s)", "", 1)

Now I just need to do some math on the days and minutes where days are
multiplied by 24 and minutes are divided by 60 so I can add them all
together.
 
R

Ron Rosenfeld

I have a string in the format 01 Day(s) 08 Hour(s) 34 Minutes, how can
I parse out just the numbers so i can convert them and add them
together so i would get 24+8+0.56?

Any help appreciated, Thanks.
Click to expand...

One way:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula:

=SUMPRODUCT(--REGEX.MID(A1,"\d+",{1,2,3}),{24,1,0.0166667})


--ron
 
R

Ron Rosenfeld

I have a string in the format 01 Day(s) 08 Hour(s) 34 Minutes, how can
I parse out just the numbers so i can convert them and add them
together so i would get 24+8+0.56?

Any help appreciated, Thanks.
Click to expand...

If your format is, indeed, EXACTLY as you display,

2 digit day
<space>
Day(s)
<space>
2 digit hour
<space>
Hours(s)
<space>
2 digit minute

then you could use the formula:

=LEFT(A1,2)*24+MID(A1,11,2)+MID(A1,22,2)/60

But any slight change in the format render this formula useless.


--ron
 
Top