Extract Number From String

[khikha1]

Hi all

please need a Vba Code or an excel formula, that the will extract numbers ( could be more than one number ) from string and Sum the numbers, some have no number so sum should be zero. below is a sample of my data, Data is in column N

hope you can assit and thanks in advance.

driver has lifted it on transit back slowly as advised by workshop

Fan blade 20,000 fan blade adjuster 15,000 engine pully 15,000

noozle 12,000 labour 3,000

ballon wing 15,000

clutch plate 7,000 labour 8,000

clutch plate 6,000 cranck shaft oil seal 3,000 labour 5,000

guarantor sent on 7th January.to resolve it couldn’t.Mr ezra sent on 9th resolving it.Issue resolved battery and money for diesel sent for him to move,truck left Akwanga moving towards Abuja

Driver raised it and he was bringing it back slowly got to ilorin could no continue is towing it back

Truck shaft 10,000 labour 7,000, other 15,000

release bearing 8,000 labour 5,000

clutch plate 12,000 fly wheel bearing 3,000 labour 2,000

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub khikha()
   Dim Cl As Range
   Dim Sp As Variant
   Dim i As Long
   
   For Each Cl In Range("N2", Range("N" & Rows.Count).End(xlUp))
      Sp = Split(Cl)
      For i = 0 To UBound(Sp)
         If IsNumeric(Sp(i)) Then Cl.Offset(, 1).Value = Cl.Offset(, 1).Value + Sp(i)
      Next i
   Next Cl
End Sub

 

[mikerickson]

This UDF should work

Function SumOfSubNumbers(aString As String) As Double
    Dim Words As Variant, oneWord As Variant
    Words = Split(Replace(aString, ",", vbNullString), " ")
    For Each oneWord In Words
        SumOfSubNumbers = SumOfSubNumbers + Val(oneWord)
    Next oneWord
End Function

 

[khikha1]

Hi & welcome to MrExcel.
How about

Sub khikha()
   Dim Cl As Range
   Dim Sp As Variant
   Dim i As Long
  
   For Each Cl In Range("N2", Range("N" & Rows.Count).End(xlUp))
      Sp = Split(Cl)
      For i = 0 To UBound(Sp)
         If IsNumeric(Sp(i)) Then Cl.Offset(, 1).Value = Cl.Offset(, 1).Value + Sp(i)
      Next i
   Next Cl
End Sub

thank you fluff for quick response . perfect.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[kweaver]

For my education, Fluff: why doesn't the IF need and End If? And, if the OP wanted a 0 rather than a blank, what change would be made?

 

[Fluff]

You only need an End If when you are using a block if (multi line), as that's a one line If, it doesn't need the End If.
To get a zero value, I'd just add

Cl.offset(,1)=0

as a new line before the if