asked on

Follow Up ? -> 2 Tables Comparison

Please See:
https://www.experts-exchange.com/questions/21059341/Follow-Up-2-Tables-Comparison.html
https://www.experts-exchange.com/questions/21054826/Expanding-on-2-Tables-Comparison.html
https://www.experts-exchange.com/questions/21054509/2-Tables-Comparison.html
------------------------------------------------------------------------------------------

Last time i promiss, I want to change the new code (See Below) to select 3 characters (instead of 2) to compare after the numbers... again ignoring spaces and caps...
Please see the previous questions to understand what it is I am trying to accomplish here...
Thanks In advance
-BassKozz

<<<START CODE>>>
Function Val1Char(strInput As String) As String
Dim i As Integer
Dim StrLenght As Integer
strInput = Replace(strInput, " ", "")
For i = 1 To Len(strInput)
If Not IsNumeric(Mid(strInput, i, 1)) Then
StrLenght = i + 1
Exit For 'exit after finding the first number from left
End If
Next i

Val1Char = IIf(StrLenght = 0, "na", Left(strInput, StrLenght))

End Function
<<<END CODE>>>

Mike Eghtebas

For three characters after first number use:

Function Val1Char(strInput As String) As String
Dim i As Integer
Dim StrLenght As Integer
strInput = Replace(strInput, " ", "")
For i = 1 To Len(strInput)
If IsNumeric(Mid(strInput, i, 1)) Then
StrLenght = i + 1
Exit For 'exit after finding the first number from left
End If
Next i

Val1Char = IIf(StrLenght = 0, "na", Mid(strInput, StrLenght , 3))

End Function

Mike Eghtebas

Last example will pick:

3RTUX first number is 3, therefor will return RTU

356RTUX first number is 3, therefor will return 56R

----------------------------
To select RTU form both examples, I guess what you are asking for, try:

Function Val1Char(strInput As String) As String
Dim i As Integer
Dim StrLenght As Integer
strInput = Replace(strInput, " ", "")
For i = 1 To Len(strInput)
If Not IsNumeric(Mid(strInput, i, 1)) Then
StrLenght = i
Exit For 'exit after finding the first number from left
End If
Next i

Val1Char = IIf(StrLenght = 0, "na", Mid(strInput, StrLenght , 3))

End Function
------------
Provided that your string never starts with text characters.

Mike

Mike Eghtebas

Well, if it start with string, like:

RTUX you will get RTU using second post.
----------
If you are looking to extrach RUT from say:

ABC12RUTX

Meaning, there is going to be some numbers always, a third code is required to do that. I will wait for your response.

Mike

basskozz

ASKER

Example of what I would like it to do...

123east = 123 easxtern (123eas=123eas)
123 east = 123easxtern (123eas=123eas)
222 Smith = 222 Smitty (222smi=222smi)

Examples of what NOT to collect or match....

111 east =/ 123 east (because numbers don't match)
111 east =/ 111 eat (because "eas" doesn't equal "eat")

ASKER CERTIFIED SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

basskozz

ASKER

Mike,

Syntax Error :
Val1Char = IIf(StrLenght = 0, "na", Left(strInput, (StrLenght , 2)))

basskozz

ASKER