• 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

New 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

mohadin

Active Member
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
 

bijicha

New Member
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

Excel Ninja
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 !​
 

shrivallabha

Excel Ninja
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

Active Member
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
 

bijicha

New Member
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
 

bijicha

New Member
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

New Member
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
 

mohadin

Active Member
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
 
Top