• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract numbers from a string and sum it together

bijicha

Member
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
 

Attachments

  • total time calculation in minutes.xlsx
    13.7 KB · Views: 8
Hi
Try
Code:
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
 
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..
 
Hi !​
Code:
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 !​
 
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 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..
Code:
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
 
Code:
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
 
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
 
Hi !​
Code:
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
 
With respecting the speed of calculation
The above code
for Top speed
Code:
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
 
Back
Top