CODEWITHSUNDEEP
vbaexcel
aaaabbbb
aaaabbbb

Reputation: 133

locate exact word in excel VBA

I have more than one text with the word "123" inside Textabc

like a123b , c123erf and 123

but I just wanna locate the exact word "123" 

Text_u1 = Mid(Textabc, InStr(Text, "123"))

I tried &123& but not working

Thanks

Upvotes: 0

Views: 301

Answers (3)

paul bica
paul bica

Reputation: 10715

Option Explicit

Sub GetWord()
    Dim Textabc As String, s As Variant, i As Variant, abc As String, sz As Long
    Dim foundStart As Long, foundLen As Long

    Textabc = "like a123b , c123erf and 123"
    abc = "123"

    s = Split(Textabc)

    For i = 0 To UBound(s)

        sz = sz + Len(s(i)) + 1

        If Trim(s(i)) = abc Then

            foundStart = sz - Len(s(i))

            foundLen = Len(Textabc) - (sz - Len(s(i))) + 1

            Debug.Print "Textabc length: " & Len(Textabc)
            Debug.Print "foundStart: " & foundStart
            Debug.Print "foundLen: " & foundLen
            Debug.Print Mid(Textabc, foundStart, foundLen)
        End If
    Next
End Sub

Upvotes: 2

Tom
Tom

Reputation: 9878

You can try using Regular Expressions

Sub Test()
    Dim regEx As Object
    Dim str As String

    Set regEx = CreateObject("vbscript.RegExp")

    str = "a123b , c123erf and 123"

    With regEx
        .Global = True
        .IgnoreCase = True
        .Pattern = "\b(123)"

        Debug.Print regEx.Execute(str)(0).FirstIndex + 1
    End With
End Sub

This will return the position of the first match it finds that is equal to just 123 everything else will be ignored. If there are more then one match you will need to loop over the output of regEx.Execute(str) to get each position

Upvotes: 1

Vityata
Vityata

Reputation: 43585

Try one of these two, depending on what you want:

Public Sub TestMe()        
    Debug.Print InStr("a123b", "123")
    Debug.Print Mid("a123b", InStr("a123b", "123"))        
End Sub

Upvotes: 1

Related Questions