Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting mixed chains of numbers and letters
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting mixed chains of numbers and letters
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting mixed chains of numbers and letters
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting mixed chains of numbers and letters
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I receive Exel file it is mixed numbers & letters Not English | Excel Discussion (Misc queries) | |||
Counting how many numbers and letters appear in a particular row | Excel Discussion (Misc queries) | |||
The order of numbers mixed with letters | Excel Discussion (Misc queries) | |||
Sort mixed numbers/letters | Excel Discussion (Misc queries) | |||
Counting numbers and letters | Excel Programming |