Extract Number From String

khikha1

New Member
Joined
Jun 10, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,720
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,078
This UDF should work
VBA Code:
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

New Member
Joined
Jun 10, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,720
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,118
Office Version
  1. 365
  2. 2010
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,720
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
Cl.offset(,1)=0
as a new line before the if
 

Forum statistics

Threads
1,143,840
Messages
5,721,100
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top