Counting mixed chains of numbers and letters

D

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
 
T

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
 
D

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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top