Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
recognizing double digit numbers in strings
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
recognizing double digit numbers in strings
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) "DB" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
recognizing double digit numbers in strings
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
recognizing double digit numbers in strings
That works great, thanks for the help
"Bob Phillips" wrote: 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) "DB" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
recognizing double digit numbers in strings
One mo
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 DB wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering 16 digit numbers - last digit turns to zero | New Users to Excel | |||
add a double digit number together | Excel Discussion (Misc queries) | |||
recognizing double digit numbers in code | Excel Discussion (Misc queries) | |||
Recognizing 2-digit years | Excel Discussion (Misc queries) | |||
Detect double digit | Excel Programming |