Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Extract Text From a String

  1. #1
    Join Date
    5th July 2005
    Posts
    325

    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.
    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,063

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    5th July 2005
    Posts
    325

    Re: "Find" method in VBA?

    See test.xls file with input and requested output (when I give a string "sot")
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,802

    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 at 21:34.
    Regards,
    Batman.

  5. #5
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,063

    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 at 21:41.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    5th July 2005
    Posts
    325

    Re: "Find" method in VBA?

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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,063

    Re: "Find" method in VBA?

    Hi

    this works, but I reckon it could be simpler.

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    5th July 2005
    Posts
    325

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: "Find" method in VBA?

    tqv1969, THREAD HIJACK Please read THIS PAGE IN FULL.

    All posts have been deleted.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Extract Part Of Text String
    By suaiko9999 in forum EXCEL HELP
    Replies: 34
    Last Post: August 17th, 2007, 17:52
  2. Extract Text From Alphanumeric String
    By davecurtis in forum EXCEL HELP
    Replies: 4
    Last Post: July 16th, 2007, 20:01
  3. Extract Text From String-vba
    By krishna.a in forum EXCEL HELP
    Replies: 2
    Last Post: May 2nd, 2007, 23:43
  4. Extract Text After Space In String
    By stevekirk in forum EXCEL HELP
    Replies: 6
    Last Post: October 6th, 2006, 10:43
  5. Extract Specific Words From a Text String
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: July 4th, 2005, 19:36

Bookmarks

Posting Permissions

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