Share via

query extract a specific text string from a text field

Anonymous
2018-08-21T18:46:24+00:00

hello,

I need access query help to return certain text from a text field. Return only text after PROC or after # and remove everything after a number like #10 criteria could be a space. Thanks

TEXT FIELD RESULTS WANTED
1. <br><br>COMFORM TO DRAWING PER NUMBER <br><br>VIA Z1-20697-6, PROC #10 <br><br> <br><br>2. <br><br>RUN A REPORT AFTER <br><br> <br><br> <br><br>NOTE: USE CAUTION WHEN HANDLING THIS STEP #10
PULLED DATA FOR ANALYSIS <br><br> VIA Z1-20697-6, PROC #5 <br><br> <br><br>RECORD NUMBER OF HITS. <br><br>. #5
Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-08-22T08:23:37+00:00

    Seems like this simple expression will do in your query:

    Result:  Val(Mid(Nz([YourTextFieldName]),1+Instr(Nz([YourTextFieldName]),"#")))

    or:

    Result:  Val(Mid(Nz([YourTextFieldName]),1+Instr(Nz([YourTextFieldName]),"PROC #")))

    1 person found this answer helpful.
    0 comments
  2. Duane Hookom 26,820 Reputation points Volunteer Moderator
    2018-08-21T19:30:45+00:00

    I expect you might need a user-defined function for this. 

    • Create a new module or use and existing one 
    • Paste this code into the module

    Function ExtractCharacters(strOriginal As String, strBegChar As String, strEndChar As String) As String

        Dim intBeg As Integer

        Dim intEnd As Integer

        intBeg = InStr(1, strOriginal & strBegChar, strBegChar)

        If intBeg = Len(strOriginal & strBegChar) Then

            'the character was not found

            ExtractCharacters = ""

         Else

            intEnd = InStr(intBeg, strOriginal & strEndChar, strEndChar)

            ExtractCharacters = Mid(strOriginal, intBeg, intEnd - intBeg)

        End If

    End Function

    • Compile and save the module
    • Use the function almost anywhere with syntax like:

     ExtractCharacters([Text Field],"#"," ")

    You can test this function in the immediate window to make sure it works.

    1 person found this answer helpful.
    0 comments
  3. Duane Hookom 26,820 Reputation points Volunteer Moderator
    2018-08-21T20:44:31+00:00

    You can create a new function like:

    Function ExtractNumbers(strOriginal As String, strBegChar As String) As String

        ' extractNumbers("VIA Z1-20697-6, PROC #5333e  asdf","#") = "#5333"

        Dim intBeg As Integer

        Dim intPosition As Integer

        Dim strReturn As String

        Dim strChar As String

        intBeg = InStr(1, strOriginal & strBegChar, strBegChar)

        If intBeg = Len(strOriginal & strBegChar) Then

            'the character was not found

            ExtractNumbers = ""

         Else

            intPosition = InStr(1, strOriginal & strBegChar, strBegChar) + 1

            strChar = Mid(strOriginal, intPosition, 1)

            strReturn = strBegChar & strChar

            Do Until Not IsNumeric(strChar)

                intPosition = intPosition + 1

                strChar = Mid(strOriginal, intPosition, 1)

                If IsNumeric(strChar) Then 'we found a number

                    strReturn = strReturn & strChar

                 Else

                    Exit Do

                End If

            Loop

            ExtractNumbers = strReturn

        End If

    End Function

    0 comments
  4. Anonymous
    2018-08-21T19:56:30+00:00

    thanks for your help. I did your module method and some work, but there are other that also return text after #10. I am guessing it might not be like space but rather something like "ALT+ENTER" key in excel.

    How can I modify so it will delete all Value after #10?

    0 comments