none
Search a long text field for a value and return surrounding text. RRS feed

  • Question

  • Here is my dilemma and I do apologize in advance as I am really rusty in Access.  I want to execute a macro to search for a text in a long text formatted cell and return that value and any X number of characters behind and in front of the word along with the word itself.

    For example.  Lets say I have a textbox with the following text.

         "Mike has a ball in his car.  The ball was purchased 3 days ago"

    Lets say the X value is 4 and the search term is "ball".

    On a click of a button I want two values to be produced from that cell.

          Value 1 = 's a ball in '
          Value 2 = 'The ball was'

    Can this be done?  Has anyone seen anything like this?  Thanks for your time.
    • Moved by David_JunFeng Monday, June 20, 2016 3:06 PM
    • Edited by Amedean Monday, September 5, 2016 1:27 PM Grammar
    Friday, June 17, 2016 2:36 AM

Answers

  • This is perfect for regular expressions.  This will give you what you want.

    Sub CharOnSides()
    
      Dim regEx As Object
      Dim matches As Object
      Dim match As Object
    
      Set regEx = CreateObject("vbscript.regexp")
      regEx.Global = True
      regEx.IgnoreCase = True
      regEx.Pattern = "(.{4}ball.{4})"
      Set matches = regEx.Execute("Mike has a ball in his car.  The ball was purchased 3 days ago")
      For Each match In matches
        Debug.Print match
      Next match
    End Sub

    • Marked as answer by Amedean Saturday, February 11, 2017 12:40 AM
    Monday, June 20, 2016 7:33 PM

All replies

  • Can this be done?  Has anyone seen anything like this?  Thanks for your time.

    Hi Amedean,

    I am not familiar with macros, but in VBA it is possible.

    But how you want the result displayed? If you only want the first match then a function is appropiate, returning that first result.

    But you can have many matches in one text. One possibility is to write the matches in a temp table, another is to write the matches in an (external) document, still another is the concatenate all the results and display it in some kind of Message form.

    A simple untested example would be:

    Sub Find_matches(cur_text As String, cur_find As String, nr_chars As Integer)
      Dim split_arr() As String
      Dim x As Integer
      Dim result As String
      
      split_arr = Split(cur_text, cur_find)
      
      For x = 0 To UBound(split_arr) - 1
        If (split_arr(x) = cur_find) Then
          'this is a match
          result = split_arr(x)
          If (x > 0) Then result = Right(split_arr(x - 1), nr_chars) & result
          If (x < UBound(split_arr) - 1) Then result = result & Left(split_arr(x + 1), nr_chars)
          
          'Do something with the result
          
        End If
      Next
    End Sub
    

    It is not yet accounted for if the length of the previous or next split part is less than nr_chars. In that case you have add the missing characters from the adjacent split parts.

    Imb.

    Friday, June 17, 2016 8:42 AM
  • Thanks for taking a stab at it!  I'll play with the code and test it out. 

    In my case, I will try to pick up all matches in a text to do a secondary search.  As long as its possible to extract text surrounding every match X distance apart then I imagine the rest is very possible. 

    Thank you Sir!
    Friday, June 17, 2016 1:16 PM
  • Hi. You could also try using Regular Expressions.

    PS. Actually, this one might be a better choice. Just a thought...



    • Edited by .theDBguy Friday, June 17, 2016 2:45 PM
    Friday, June 17, 2016 2:36 PM
  • Man, you guys are awesome!  That looks very promising.  It will be a while while I play with the code but I will see what I can get to work here.
    Friday, June 17, 2016 10:11 PM
  • Good luck and let us know how it goes.
    Saturday, June 18, 2016 1:17 PM
  • Amedean –

    Take a look at concordance generating software…


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Saturday, June 18, 2016 4:06 PM
  • Hi Amedean,

    This is the forum to discuss questions and feedback for Access for Developers, I'll move your question to the MSDN forum for Visual Basic for Applications (VBA)

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thank you for your understanding.
    Monday, June 20, 2016 2:14 AM
  • This is perfect for regular expressions.  This will give you what you want.

    Sub CharOnSides()
    
      Dim regEx As Object
      Dim matches As Object
      Dim match As Object
    
      Set regEx = CreateObject("vbscript.regexp")
      regEx.Global = True
      regEx.IgnoreCase = True
      regEx.Pattern = "(.{4}ball.{4})"
      Set matches = regEx.Execute("Mike has a ball in his car.  The ball was purchased 3 days ago")
      For Each match In matches
        Debug.Print match
      Next match
    End Sub

    • Marked as answer by Amedean Saturday, February 11, 2017 12:40 AM
    Monday, June 20, 2016 7:33 PM