# Extract Number From String

#### khikha1

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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
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
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
Hi & welcome to MrExcel.
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

Glad we could help & thanks for the feedback.

#### kweaver

##### Well-known Member
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
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

Replies
1
Views
147

1,141,715
Messages
5,708,021
Members
421,540
Latest member
quocbinh

### 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.

### Which adblocker are you using?

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

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