Extract numbers from a string and sum it together

[bijicha]

Hi Team,

I want to extract the days, hours and minutes from cell string value "0 Day(s) 3 hour(s) 39 min(s)" and multiply Day(s) with 1440 plus hour(s) with 60 plus min(s) and to get the sum of n number of cells..

Can anyone help with a formula or VBA to do this.. Attached file with details

 

[mohadin]

Hi
Try

Sub test()
Dim a, i, x, t
    a = Range("b2:b8")
    For i = 1 To UBound(a)
        x = Split(a(i, 1), ") ")
        t = t + Split(x(0))(0) * 1440 + Split(x(1))(0) * 60 + Split(x(2))(0)
    Next
    Cells(11, 2) = t & "mins"
End Sub

 

[bijicha]

Hi Mohadin,

That was wonderful , and worked perfectly ..
Just a question, instead of static rows Range("b2:b8") , how it will be a dynamic range according to more data, i mean, some times, it will till b100 or b1000..

Thanks..

 

[Marc L]

Hi !​

Sub Demo1()
         Dim V, L&
    For Each V In Range("B2", [B1].End(xlDown)).Value2
        V = Filter(Split(V), "(s)", False)
        If UBound(V) = 2 Then L = L + V(0) * 1440 + V(1) * 60 + V(2)
    Next
        [B1].End(xlDown)(3).Value2 = L & " mins"
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[shrivallabha]

With your data you can probably try below formula and see if it helps your situation.

=SUMPRODUCT(LEFT(B2:B8,FIND(" ",B2:B8,1)-1)*1440)+SUMPRODUCT(TRIM(MID(SUBSTITUTE(B2:B8," ",REPT(" ",99)),99*2,99))*60)+SUMPRODUCT(TRIM(MID(SUBSTITUTE(B2:B8," ",REPT(" ",99)),99*4,99))*1)

 

[mohadin]

Hi Mohadin,

That was wonderful , and worked perfectly ..
Just a question, instead of static rows Range("b2:b8") , how it will be a dynamic range according to more data, i mean, some times, it will till b100 or b1000..

Thanks..

Sub test()
Dim a, i, x, t
    a = Range(Cells(2, 2), Cells(2, 2).End(xlDown))
    For i = 1 To UBound(a)
    On Error Resume Next
        x = Split(a(i, 1), ") ")
        t = t + Split(x(0))(0) * 1440 + Split(x(1))(0) * 60 + Split(x(2))(0)
    Next
    Cells(UBound(a) + 4, 2) = t & "mins"
End Sub

 

[bijicha]

Sub test()
Dim a, i, x, t
    a = Range(Cells(2, 2), Cells(2, 2).End(xlDown))
    For i = 1 To UBound(a)
    On Error Resume Next
        x = Split(a(i, 1), ") ")
        t = t + Split(x(0))(0) * 1440 + Split(x(1))(0) * 60 + Split(x(2))(0)
    Next
    Cells(UBound(a) + 4, 2) = t & "mins"
End Sub

Hi Mohadin,

Yes, perfectly worked.. Appreciated .. Stay safe......

Thanks

 

[bijicha]

With your data you can probably try below formula and see if it helps your situation.

=SUMPRODUCT(LEFT(B2:B8,FIND(" ",B2:B8,1)-1)*1440)+SUMPRODUCT(TRIM(MID(SUBSTITUTE(B2:B8," ",REPT(" ",99)),99*2,99))*60)+SUMPRODUCT(TRIM(MID(SUBSTITUTE(B2:B8," ",REPT(" ",99)),99*4,99))*1)

Hi Shri,

Thank you very much, your formula worked to get the results, i need a VBA to make it dynamic..

Appreciated your suggestion

 

[bijicha]

Hi !​

Sub Demo1()
         Dim V, L&
    For Each V In Range("B2", [B1].End(xlDown)).Value2
        V = Filter(Split(V), "(s)", False)
        If UBound(V) = 2 Then L = L + V(0) * 1440 + V(1) * 60 + V(2)
    Next
        [B1].End(xlDown)(3).Value2 = L & " mins"
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

Hi Marc,

Your code worked well for my requirement, Appreciate the help

Thanks

 

[mohadin]

With respecting the speed of calculation
The above code
for Top speed

Sub test()
    Dim a, i, x, t
    a = Range(Cells(2, 2), Cells(2, 2).End(xlDown))
    For i = 1 To UBound(a)
        On Error Resume Next
        x = Split(a(i, 1))
        t = t + x(0) * 1440 + x(2) * 60 + x(4)
    Next
    Cells(UBound(a) + 4, 2) = t & " mins"
End Sub