Counting mixed chains of numbers and letters

[Dave]

Is there a way to sum the numerical values in a chain that includes numbers
and letters.

Example.
If I have a chain such as "M4G3M4P0" is there something I can do so that
excel will sum the numerical values? Note: the chains I am working with are
sometimes quite long (up to 25 characters) but almost always go
letter-number-letter-number- and so on.

Thank you

 

[Tom Ogilvy]

Sub Sumcharacters()
Dim i as Long, s as String
Dim lsum as Long
for i = 1 to len(cell.value)
s = Mid(cell.value,i,1)
if isnumeric(s) then
lsum = lsum + clng(s)
end if
Next
msgbox lsum
End Sub

 

[Dave]

Tom-
Thank you for the code for VB. I am fairly unfamiliar with VB though, if my
string of "M4G3 M4P0" was in cell B4, what in the VB code do I need to
change. Can I change the code so it evaluates a range of cells?
Thanks again

 

[Tom Ogilvy]

Sub Sumcharacters()
Dim i as Long, s as String
Dim lsum as Long, cell as range

set cell = Range("B4")
for i = 1 to len(cell.value)
s = Mid(cell.value,i,1)
if isnumeric(s) then
lsum = lsum + clng(s)
end if
Next
msgbox lsum
End Sub


Sub SumcharactersMultipleCells()
Dim i as Long, s as String
Dim lsum as Long, cell as Range
set cell = Range("B4").Resize(5,20)
for each cell in rng
for i = 1 to len(cell.value)
s = Mid(cell.value,i,1)
if isnumeric(s) then
lsum = lsum + clng(s)
end if
Next
Next cell
msgbox lsum
End Sub