Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract Text (XP)

    Is there a way to extract a certain number of characters from a text field based on a key word?

    For example, a text field (cell) contains 2000 characters (no pattern to how the words are arranged). Within some of the cells are a key word I am looking for. I need that key word plus the 25 following characters displayed in a separate cell. Also, the key word may occur more than one time per cell, and I need each occurance of this key word in the cell to have the same function applied.

    Thanks in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Text (XP)

    What exactly do you mean by "I need each occurance of this key word in the cell to have the same function applied"? Do you want each occurrence plus 25 following characters to be displayed in a separate cell, i.e. if the keyword occurs 3 times, there are three cells containing the keyword+25 characters?

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract Text (XP)

    Try something like this:
    =IF(ISERROR(MID(A2,SEARCH($B$1,A2),LEN($B$1)+25)), "",MID(A2,SEARCH($B$1,A2),LEN($B$1)+25))
    Enter the formula into B2. It will search for any word that you type into cell B1and extract it + 25 following characters into Cell B2. It searches info in the A column!
    PS: This does not YET cover for duplicate entries of the word in a single cell!
    Regards,
    Rudi

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract Text (XP)

    Hi There

    I have put this little bit of code together. It currently is doing doing a search in Column A and pasting the text in column B nect to that entry where the text is found. I will adapt if you like as there is a problem if the word you are searching for falls within the last 50 characters of the string text. Have a look, is this the type of thing you are looking for?

    Sub FindText()
    Dim strText As String
    Dim lngMaxRow As Integer
    Dim strString As String
    Dim strNew As String


    strText = Application.InputBox("Enter word you wish to search for")

    lngMaxRow = Range("A65536").End(xlUp).Row

    Range("A1").Select
    For i = 1 To lngMaxRow

    Cells.Find(What:=strText).Activate

    strString = ActiveCell.Value

    j = InStr(1, strString, strText)

    strNew = Mid(strString, j, 50 - Len(strText))

    ActiveCell.Offset(0, 1).Select

    ActiveCell.Value = strNew

    ActiveCell.Offset(1, -1).Select

    Next i
    End Sub
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •