Extract Text From a String

[jonny]

I would like to extract whole word according to starting string.
I wrote following code, but function "find" is not exist.

Sub GetFullName() 
    Dim str As String 
    str = InputBox("Requested String?") 
     
    Range("A2").Select 
    Range(Selection, Selection.End(xlDown)).Select 
     
    For Each rng In Selection 
        rng.Offset(0, 1) = Mid(A2, Find(str, A2, 1), Find(" ", A2, Find(str, A2, 1)) - Find(str, A2, 1)) 
    Next rng 
End Sub 

Please help to fix it.

[Wigi]

Hi

I cannot understand what you want to do exactly.

A simple workbook might be helpful.

(Also, rng is not defined.)

Wigi

[jonny]

See test.xls file with input and requested output (when I give a string "sot")

[Batman]

jonny,

The equivalent to the FIND worksheet function in VBA is InStr. The arguments for this (at least the ones that are usually used) are:
1) start position: set to 1 if looking from the start of the text
2) text to look in: in your case, something like 'rng.Value'
3) the text to find: in your case " ".

As with Wigi, I can't quite understand what you'r trying to achieve, but hopefully this explanation of the InStr will enable you to do it.

By the way, your 'test.xls' file wasn't attached.

[Wigi]

EDIT: wrong post, I understand the purpose now.

And what should be in the column next to it?
Everything from column B, except that what you're looking for? SOT in this case.

Wigi

[jonny]

Ignore column "B", your arguments only are column "B" and requested string.

[Wigi]

Hi

this works, but I reckon it could be simpler.

Sub GetFullName() 
    Dim str As String, rng As Range, beginPos As Integer, endPos As Integer 
    str = InputBox("Requested String?") 
    For Each rng In Range("A2", Range("A2").End(xlDown)) 
        beginPos = InStr(1, rng.Value, str) 
        On Error Resume Next 
        endPos = InStr(beginPos, rng.Value, " ") 
        On Error Goto 0 
        If endPos = 0 Then endPos = Len(rng.Value) + 1 
        If InStr(1, rng.Value, str) > 0 Then 
            rng.Offset(0, 1) = Mid(rng.Value, beginPos, endPos - beginPos) 
        End If 
    Next rng 
End Sub 

I'm not sure how to handle the situation where the SOT is at the end of the line (i.e. no space afterwards anymore)

Wigi

[jonny]

Wigi , thanks a lot.
That's exactly what I need, I will try to add EOL recognition to code also.

[Dave Hawley]

tqv1969, THREAD HIJACK Please read THIS PAGE IN FULL.

All posts have been deleted.