recognizing double digit numbers in strings

[DB]

i am currently working a code to add the numerical parts of strings such as
"M4G3 M4P0". A problem arises however in my code when the string contains a
double digit number(ex. M4G3 M12P0). My code will recognize this as a two
individual single digit numbers (12= 1 and 2). Any suggestions? Here is the
code I'm using.
**********
Sub Sumcharacters()
Dim i As Long, s As String
Dim lsum As Long
For i = 1 To Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
If IsNumeric(s) Then
lsum = lsum + CLng(s)
End If
Next
ActiveCell.Offset(0, 1).Value = lsum
End Sub

 

[Bob Phillips]

Sub Sumcharacters()
Dim i As Long, s As String
Dim nSum As Long
Dim lSum As Long
i = 1
Do While i <= Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
If IsNumeric(s) Then
nSum = 0
Do While IsNumeric(s)
nSum = nSum * 10 + CLng(s)
i = i + 1
s = Mid(ActiveCell, i, 1)
Loop
lSum = lSum + nSum
Else
i = i + 1
End If
Loop
ActiveCell.Offset(0, 1).Value = lSum
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Joel]

try this

Sub Sumcharacters()

Dim i As Long, s As String
Dim lsum As Long
i = 1
while i <= Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
If IsNumeric(s) Then
IsNum = 0
do while (IsNumeric(s) = true) and (i <= Len(ActiveCell))
IsNumeric(s) = (10 * lsum) + CLng(s)
i = i + 1
s = Mid(ActiveCell, i, 1)
loop
Else
i = i + 1
End If
Loop
ActiveCell.Offset(0, 1).Value = lsum
End Sub

 

[DB]

That works great, thanks for the help

Sub Sumcharacters()
Dim i As Long, s As String
Dim nSum As Long
Dim lSum As Long
i = 1
Do While i <= Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
If IsNumeric(s) Then
nSum = 0
Do While IsNumeric(s)
nSum = nSum * 10 + CLng(s)
i = i + 1
s = Mid(ActiveCell, i, 1)
Loop
lSum = lSum + nSum
Else
i = i + 1
End If
Loop
ActiveCell.Offset(0, 1).Value = lSum
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

One more:

Option Explicit
Sub Sumcharacters2()
Dim i As Long
Dim s As String
Dim lsum As Long

s = ActiveCell.Value
For i = 1 To Len(s)
If IsNumeric(Mid(s, i, 1)) Then
'do nothing
Else
Mid(s, i, 1) = " "
End If
Next i
With Application
s = .Substitute(.Trim(s), " ", "+")
If Len(s) = 0 Then
lsum = 0
Else
lsum = .Evaluate(s)
End If
End With
ActiveCell.Offset(0, 1).Value = lsum
End Sub