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

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

--
Regards,
Tom Ogilvy


"Dave" wrote:

> 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-
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" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Dave" wrote:
>
> > 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
> >

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

--
Regards,
Tom Ogilvy

"Dave" wrote:

> 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" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Dave" wrote:
> >
> > > 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
> > >