CODEWITHSUNDEEP
excelvba
Gaus Shaikh
Gaus Shaikh

Reputation: 209

Extract text after specific word

I want to retrieve the text immediately after the word "for" from a string. In the example below, I want to retrieve "GLDSK8716".

Completion Notification for GLDSK8716 - Derivative Contracts - Futures, Forwards, Swaps, and Options

I tried this formula but it retrieves all of the text after "for".

=TRIM(MID(R2,SEARCH("For",R2)+LEN("for"),255))

Upvotes: 2

Views: 10228

Answers (3)

Shai Rado
Shai Rado

Reputation: 33662

My solution is in VBA. The code below uses the RegEx to find all words between spaces. Then it loops through all words, once it finds "for" it raises a flag to indicate the next word (RegEx.Match) is the one you are looking for.

Code

Option Explicit

Sub ExtractNumbers()

Dim Reg1 As Object
Dim RegMatches As Variant
Dim Match As Variant
Dim NextWord As Boolean

Set Reg1 = CreateObject("VBScript.RegExp")
With Reg1
    .Global = True
    .IgnoreCase = True
    .Pattern = "\w{1,50}" ' Match any set of characters up to length of 50, untill a space
End With

Set RegMatches = Reg1.Execute(Range("R2").Value)
NextWord = False ' <-- reset flag
If RegMatches.Count >= 1 Then        
    For Each Match In RegMatches
        If NextWord Then
            MsgBox "The word you are looking for is '" & Match & "'"
            Exit Sub
        End If
        If UCase(Match) Like "FOR" Then NextWord = True '<-- "for" found >> raise flag up
    Next Match
End If

End Sub

Screen-shot of your string in Cell R2 and result in a MsgBox:

enter image description here

Upvotes: 0

Vityata
Vityata

Reputation: 43565

This is my version with VBA:

Option Explicit

Public Function ExtractAfterWord(rngWord As Range, strWord As String) As String

    On Error GoTo ExtractAfterWord_Error

    Application.Volatile

    Dim lngStart        As Long
    Dim lngEnd          As Long

    lngStart = InStr(1, rngWord, strWord)
    If lngStart = 0 Then
        ExtractAfterWord = "Not Found"
        Exit Function
    End If
    lngEnd = InStr(lngStart + Len(strWord) + 1, rngWord, " ")

    If lngEnd = 0 Then lngEnd = Len(rngWord)

    ExtractAfterWord = Trim(Mid(rngWord, lngStart + Len(strWord), lngEnd - lngStart - 2))



    On Error GoTo 0
    Exit Function

ExtractAfterWord_Error:

    ExtractAfterWord = Err.Description

End Function

The condition in the code (if lngEnd = 0) is to make sure, that the formula works even if the for is before the last word in the text. A "Not Found" answer is also added.

enter image description here

Upvotes: 3

Pankaj Jaju
Pankaj Jaju

Reputation: 5471

Try =MID(A1,FIND("for ",A1)+4,FIND(" ",A1,FIND("for ",A1)+4)-(FIND("for ",A1)+4))

Assuming your text is in cell A1 and the pattern is for <data> -

Upvotes: 0

Related Questions