Announcement

Collapse
No announcement yet.

Extract Text From a String

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Extract Text From a String

    I would like to extract whole word according to starting string.
    I wrote following code, but function "find" is not exist.
    Code:
    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.

  • #2
    Re: "Find" method in VBA?

    Hi

    I cannot understand what you want to do exactly.

    A simple workbook might be helpful.

    (Also, rng is not defined.)

    Wigi
    Regards,

    Wigi

    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Comment


    • #3
      Re: "Find" method in VBA?

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

      Comment


      • #4
        Re: "Find" method in VBA?

        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.
        Last edited by Batman; August 21st, 2006, 20:34.
        Regards,
        Batman.

        Comment


        • #5
          Re: "Find" method in VBA?

          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
          Last edited by Wigi; August 21st, 2006, 20:41.
          Regards,

          Wigi

          Excel MVP 2011-2014

          For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

          -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

          Comment


          • #6
            Re: "Find" method in VBA?

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

            Comment


            • #7
              Re: "Find" method in VBA?

              Hi

              this works, but I reckon it could be simpler.

              Code:
              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
              Regards,

              Wigi

              Excel MVP 2011-2014

              For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

              -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

              Comment


              • #8
                Re: "Find" method in VBA?

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

                Comment


                • #9
                  Re: "Find" method in VBA?

                  tqv1969, THREAD HIJACK Please read THIS PAGE IN FULL.

                  All posts have been deleted.

                  Comment

                  Working...
                  X