Extract from string

[jackjee]

Hi All
I have a VBA code in access which I am using to split a string and inserting the results to a table.
the string will have alphabets and numbers. If the first 3 characters are alphabets and the 4th character is a number I need to extract the first 3 characters. And if the string has all alphabets or numbers or numbers before 3rd characters, insert the same string to the field. But if the string has 3 alphabets, then numbers and then alphabets, I need only the first 3 alphabets extracted to my table. My VBA code is not doing this. Hope someone can guide me what I am missing. Please note that the code I got was from internet and modified to accommodate my use.

 Dim OldString As String
    Dim NewString As String
    Dim rstAlpha As Recordset
    Dim rstAlphaNum As Recordset
    Dim I As Integer
    DoCmd.RunSQL "Delete * from ResultTbl"
 
    Set rstAlpha = CurrentDb.OpenRecordset("ResultTbl", dbOpenDynaset)
    Set rstAlphaNum = CurrentDb.OpenRecordset("MyDataTbl", dbOpenDynaset)
 
    rstAlphaNum.MoveFirst
 
    Do While rstAlphaNum.EOF = False
        OldString = rstAlphaNum![MyStringitem]
        NewString = ""
        For I = 1 To Len(OldString)
 
 
         If Not IsNumeric(Mid(OldString, I, 4)) Then
             NewString = NewString & (Mid(OldString, I, 1))
 
          End If
        Next I
        rstAlpha.AddNew
        rstAlpha![ExtractedString] = NewString
        rstAlpha.Update
        rstAlphaNum.MoveNext
    Loop
    MsgBox "Finished"
    'DoCmd.OpenTable "ResultTbl", acViewNormal

[isladogs]

Please give some sample data and the required result in each case to clarify what you want.

[NeoPa]

On similar lines as IslaDogs - What does your question even mean. I've read it a couple of times and all I got was head pain & confusion.

I get that when you refer to alphabets you are actually referring to alphabetic characters (Or more simply just letters.) but the rest was so unclear I gave up.

Before I leave you with just this complaint let me just add that we are probably going to be able to help once we have a decent question to work with. I'd be surprised if the use of the Like comparison didn't come into it somewhere, but more detail than that will depend on the actual requirement.

[cactusdata]

1. If the first 3 characters are alphabets and the 4th character is a number I need to extract the first 3 characters.
2. If the string has all alphabets or numbers or numbers before 3rd characters, insert the same string to the field.
3. 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.

[jackjee]

Hi cactusdata
Thank you for the provided function. I used it in a query and it looks like it is giving expected results
The syntax of query as "SELECT data.alphabets, ExtractThreeOrMore([alphabets]) AS Result
FROM data;"
The sample records in the table as below
Field name: alphabets

1NTRWE
NNB3456
7DCXD
NNB67FX
DUC6
LIY683
LIY9RT
MM2345
LO8765F

And the results I got in the query as below
Field Name: Result
1NTRWE
NNB
7DCXD
NNB
DUC
LIY
LIY
MM2345
LO8765F

I think it is working fine. I need to test it with large data and also need to try how to add this function in my old code.
Thank you so much for interpreting my question correctly and provided a quick solution. 'bytes' always very supportive

[cactusdata]

You are welcome! Get back if you have questions.

[nhakhoaparis]

Wow thanks for share

[SioSio]

Another way using "RegExp"

Function ExtractData(ByVal s As String) As String
    Dim RegExp As Object
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.Pattern = "^[a-zA-Z]+$"
    If RegExp.test(Left(s, 4)) Then
        ExtractData = s
    ElseIf Not RegExp.test(Left(s, 3)) Then
        ExtractData = s
    Else
        ExtractData = Left(s, 3)
    End If
    Set RegExp = Nothing
End Function

 s = ExtractData("1NTRWE")