- If the first 3 characters are alphabets and the 4th character is a number I need to extract the first 3 characters.
- If the string has all alphabets or numbers or numbers before 3rd characters, insert the same string to the field.
- If the string has 3 alphabets, then numbers and then alphabets, I need only the first 3 alphabets.
1 and 3 are the same, so you can cut it down to check for a digit in the first three characters.
If found, pull the full string; if not pull the first three letters if the fourth is a digit:
- Public Function ExtractThreeOrMore(ByVal Value As String) As String
-
-
Dim Position As Integer
-
Dim Result As String
-
Dim DigitExists As Boolean
-
-
If Len(Value) >= 3 Then
-
For Position = 1 To 3
-
If IsNumeric(Mid(Value, Position, 1)) Then
-
DigitExists = True
-
Exit For
-
End If
-
Next
-
End If
-
-
If DigitExists Then
-
Result = Value
-
Else
-
If IsNumeric(Mid(Value, 4, 1)) Then
-
Result = Left(Value, 3)
-
End If
-
End If
-
-
ExtractThreeOrMore = Result
-
-
End Function
Now, use any method to insert the result in your table.
