basskozz
asked on
Follow Up ? -> 2 Tables Comparison
please see:
https://www.experts-exchange.com/questions/21054826/Expanding-on-2-Tables-Comparison.html
--------------------------
How can I change the following code to select 2 alpha characters after the number (again ignoring spaces) ?
Function Val1Char(strInput As String) As String
Dim strReturn
Dim CurVal As String
For i = 1 To Len(strInput)
CurVal = Mid(strInput, i, 1)
If IsNumeric(CurVal) Then
strReturn = strReturn & CurVal
Else
If IsNumeric(CurVal) = False And CurVal <> " " Then
strReturn = strReturn & CurVal
Exit For
End If
End If
Next i
Val1Char = strReturn
End Function
https://www.experts-exchange.com/questions/21054826/Expanding-on-2-Tables-Comparison.html
--------------------------
How can I change the following code to select 2 alpha characters after the number (again ignoring spaces) ?
Function Val1Char(strInput As String) As String
Dim strReturn
Dim CurVal As String
For i = 1 To Len(strInput)
CurVal = Mid(strInput, i, 1)
If IsNumeric(CurVal) Then
strReturn = strReturn & CurVal
Else
If IsNumeric(CurVal) = False And CurVal <> " " Then
strReturn = strReturn & CurVal
Exit For
End If
End If
Next i
Val1Char = strReturn
End Function
with some modifications to mike's solution
Function Val1Char(strInput As String) As String
Dim LastNumLoc As Integer, i As Integer
Dim sInput As String
sInput = Replace(strInput, " ", "")
For i = 1 To Len(strInput)
If Not IsNumeric(Mid(sInput, i, 1)) Then
LastNumLoc = i
Exit For 'exit after finding the first number from left
End If
Next i
Val1Char = IIf(LastNumLoc = 0, "na", Mid(sInput, (LastNumLoc), 2))
End Function
Function Val1Char(strInput As String) As String
Dim LastNumLoc As Integer, i As Integer
Dim sInput As String
sInput = Replace(strInput, " ", "")
For i = 1 To Len(strInput)
If Not IsNumeric(Mid(sInput, i, 1)) Then
LastNumLoc = i
Exit For 'exit after finding the first number from left
End If
Next i
Val1Char = IIf(LastNumLoc = 0, "na", Mid(sInput, (LastNumLoc), 2))
End Function
oops,
use this
Function Val1Char(strInput As String) As String
Dim FirstAlphaLoc As Integer, i As Integer
Dim sInput As String
sInput = Replace(strInput, " ", "")
For i = 1 To Len(strInput)
If Not IsNumeric(Mid(sInput, i, 1)) Then
FirstAlphaLoc = i
Exit For 'exit after finding the first number from left
End If
Next i
Val1Char = IIf(FirstAlphaLoc = 0, "na", Mid(sInput, (FirstAlphaLoc), 2))
End Function
use this
Function Val1Char(strInput As String) As String
Dim FirstAlphaLoc As Integer, i As Integer
Dim sInput As String
sInput = Replace(strInput, " ", "")
For i = 1 To Len(strInput)
If Not IsNumeric(Mid(sInput, i, 1)) Then
FirstAlphaLoc = i
Exit For 'exit after finding the first number from left
End If
Next i
Val1Char = IIf(FirstAlphaLoc = 0, "na", Mid(sInput, (FirstAlphaLoc), 2))
End Function
Yes, Dim i As Integer was missing from my code. capricorn1's solution would work also if "+1" is added to:
Val1Char = IIf(FirstAlphaLoc = 0, "na", Mid(sInput, (FirstAlphaLoc+1), 2))
Mike
Val1Char = IIf(FirstAlphaLoc = 0, "na", Mid(sInput, (FirstAlphaLoc+1), 2))
Mike
Mike,
no need to add "+1" , if you add "+1" you will miss the first non numeric character and end up picking the second and the third.
no need to add "+1" , if you add "+1" you will miss the first non numeric character and end up picking the second and the third.
capricorn1,
Re:>How can I change the following code to select 2 alpha characters after the number... ' from the question above
The first numeric has to be missing if I am reading it right.
Re:>How can I change the following code to select 2 alpha characters after the number... ' from the question above
The first numeric has to be missing if I am reading it right.
I agree Mike,
but my code checks for the first occurrence of the non numeric character;
For i = 1 To Len(strInput)
If Not IsNumeric(Mid(sInput, i, 1)) Then '<<<<<<<<<<<
FirstAlphaLoc = i
but my code checks for the first occurrence of the non numeric character;
For i = 1 To Len(strInput)
If Not IsNumeric(Mid(sInput, i, 1)) Then '<<<<<<<<<<<
FirstAlphaLoc = i
Smith7CA needs to produce "CA" (two characters after first number)
Your solution will produce "Sm" not "CA", help me to understand if you are reading the problem differently.
Your solution will produce "Sm" not "CA", help me to understand if you are reading the problem differently.
the examples given are
123main st = 123maple st
456 South st = 456smith st
789East st = 789 Etstern ave
i don't see any combination like your example {Smith7CA}
123main st = 123maple st
456 South st = 456smith st
789East st = 789 Etstern ave
i don't see any combination like your example {Smith7CA}
Now I see, I should have read the content of the original question.
ASKER
let me rephrase my question, in the previous question I asked how can I select all the numbers no spaces and the FIRST (and only first) alpha character...
Now I would like to : STILL select all the starting numbers *no spaces* and the FIRST & SECOND alpha characters
[NOTE: CaPs shouldn't make a difference.... i.e. East=east]
i.e.
123main st = 123maple st (123ma=123ma)
456 Smuth st = 456smith st (456sm=456sm)
789East st = 789 Eatstern ave (789ea=789ea)
Also note, any proceeding characters after the first set of numbers and the FIRST & SECOND alpha characters should NOT be collected
Now I would like to : STILL select all the starting numbers *no spaces* and the FIRST & SECOND alpha characters
[NOTE: CaPs shouldn't make a difference.... i.e. East=east]
i.e.
123main st = 123maple st (123ma=123ma)
456 Smuth st = 456smith st (456sm=456sm)
789East st = 789 Eatstern ave (789ea=789ea)
Also note, any proceeding characters after the first set of numbers and the FIRST & SECOND alpha characters should NOT be collected
ASKER
i.e.
"123 main st" =/ (Does NOT equal) "321 main st" (preceeding numbers are different 123=/321
111 east st =/ 222 east st
etc...
"123 main st" =/ (Does NOT equal) "321 main st" (preceeding numbers are different 123=/321
111 east st =/ 222 east st
etc...
ASKER
Although...
123main 23rd st = 123 main 62st (123ma=123ma) [ignore "in 23rd st" & "in 62st"]
111 east 34th st = 111 eatstern 99th st (111ea=111ea) [ignore "st 34th st" & "tstern 99th st"]
222 smith 56th = 222smitty (222sm=222sm) [ignore "ith 56th" & "itty"]
Also note the spaces... they shouldn't play a factor at all
123main = 123 main
123 main = 123main
etc...
123main 23rd st = 123 main 62st (123ma=123ma) [ignore "in 23rd st" & "in 62st"]
111 east 34th st = 111 eatstern 99th st (111ea=111ea) [ignore "st 34th st" & "tstern 99th st"]
222 smith 56th = 222smitty (222sm=222sm) [ignore "ith 56th" & "itty"]
Also note the spaces... they shouldn't play a factor at all
123main = 123 main
123 main = 123main
etc...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dim NumLocation As Integer
For i = 1 To Len(strInput)
If IsNumeric(Mid(strInput, i, 1)) Then
NumLocation = i
Exit For 'exit after finding the first number from left
End If
Next i
Val1Char = iif(NumLocation =0,"na", Mid(strInput, (NumLocation +1), 2))
End Function