Extract a string from a paragraph

kirkm · #1 09-07-2016, 03:26 PM

Just wondering if this is possible?

The string contains upper case characters and has vbtabs either side.

Thanks. I have a routine that looks through each paragraph and assigns it to a string variable.

gmayor · #2 09-08-2016, 01:01 AM

You can extract them individually, which begs the question - what do you want to do with these strings having extracted them?

'Contains' upper case characters or 'comprises' upper case characters?

The following assumes only upper case characters & space (no punctuation)

Code:

Sub Macro1()
Dim oRng As Range
Dim oPara As Paragraph
Dim sText As String
    For Each oPara In ActiveDocument.Paragraphs
        Set oRng = oPara.Range
        With oRng.Find
            Do While .Execute(FindText:="^t[A-Z ]{1,}^t", MatchWildcards:=True)
                If oRng.InRange(oPara.Range) Then
                    sText = Replace(oRng.Text, Chr(9), "")
                    'do something with stext e.g.
                    MsgBox sText
                End If
                oRng.Collapse 0
            Loop
        End With
    Next oPara
lbl_Exit:
    Set oRng = Nothing
    Set oPara = Nothing
    Exit Sub
End Sub

kirkm · #3 09-08-2016, 01:42 AM

Thanks again Graham. That's very useful and also shows me how Reg Ex is used.
(Once I've identified the string, I'm adding more data about it into the document).

kirkm · #4 09-08-2016, 05:51 PM

Graham, been playing around with your routine and not having any luck with a line I've changed:

Do While .Execute(FindText:="^p" & "010" & "^t", MatchWildcards:=False)

It finds nothing, even though the text does exist. If I change MatchWildcards to True it reports error 5692 but no description just one little square shape. I googled for that error but found no definite reason (except perhaps it's the ^ char.)

Obviously I'm doing something wrong... can you see what it is? Possibly that syntax is for RegEx only? Something else is for finding a text match ?

Thanks.

gmayor · #5 09-08-2016, 10:06 PM

It has nothing to do with the sequence, but the fact that sequence will never appear in the current paragraph, because of ^p which makes the search across two paragraphs. Use instead

Code:

Sub Macro2()
Dim oRng As Range
Dim oPara As Paragraph
Dim sText As String
    For Each oPara In ActiveDocument.Paragraphs
        Set oRng = oPara.Range
        With oRng.Find
            Do While .Execute(FindText:="010^t")
                If oRng.start = oPara.Range.start Then
                    sText = Replace(oRng.Text, Chr(9), "")
                    'do something with stext e.g.
                    MsgBox sText
                End If
                oRng.Collapse 0
            Loop
        End With
    Next oPara
lbl_Exit:
    Set oRng = Nothing
    Set oPara = Nothing
    Exit Sub
End Sub

kirkm · #6 09-08-2016, 11:51 PM

> because of ^p which makes the search across two paragraphs

I should have figured that! Thanks for the new code. It's working as it should but isn't yet doing what I want. I should be able to figure it out (but haven't yet

).
In your example sText is "010" but what I'm after is the whole paragraph starting with 010 + vbtab.
Been hunting around on Google all day... but Find gets you info about Find and Replace currently selected text, not find something in a paragraph. And I expect the next hurdle will be how to handle multiple finds.
Anyway I will visit your website after tea and ';m sure I'll pick up some useful tips. Many thanks.

gmayor · #7 09-09-2016, 12:41 AM

If you want the whole paragraph then it is opara

Code:

Sub Macro2()
Dim oRng As Range
Dim oPara As Paragraph
Dim sText As String
    For Each oPara In ActiveDocument.Paragraphs
        Set oRng = oPara.Range
        With oRng.Find
            Do While .Execute(FindText:="010^t")
                If oRng.start = oPara.Range.start Then
                    sText = oPara.Range
                    'do something with stext e.g.
                    MsgBox sText
                End If
                oRng.Collapse 0
            Loop
        End With
    Next oPara
lbl_Exit:
    Set oRng = Nothing
    Set oPara = Nothing
    Exit Sub
End Sub

alternatively

Code:

Sub Macro3()
Dim oRng As Range
Dim sText As String
    Set oRng = ActiveDocument.Range
    With oRng.Find
        Do While .Execute(FindText:="010^t")
            If oRng.start = oRng.Paragraphs(1).Range.start Then
                sText = oRng.Paragraphs(1).Range.Text
                'do something with stext e.g.
                MsgBox sText
            End If
            oRng.Collapse 0
        Loop
    End With
lbl_Exit:
    Set oRng = Nothing
    Exit Sub
End Sub

Guessed · #8 09-11-2016, 06:13 PM

Graham's Macro3 code would be faster than the earlier alternatives of stepping through every paragraph.

I would suggest using InStr instead of .Start to get a bit more flexibility in where the found string might appear.

Code:

Sub Macro4()
  Dim oRng As Range
  Dim sText As String, sFind As String
  
  sFind = "010" & Chr$(9)
  Set oRng = ActiveDocument.Range
  With oRng.Find
    Do While .Execute(FindText:=sFind)
      sText = oRng.Paragraphs(1).Range.Text
      If InStr(sText, sFind) = 1 Then
        MsgBox sText
      End If
      oRng.Collapse 0
    Loop
  End With
lbl_Exit:
  Set oRng = Nothing
  Exit Sub
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to extract a word from an excel string with various lengths	keywestsue	Excel	3	09-18-2015 07:32 AM
How to extract only numbers from a STRING?	Learner7	Excel	3	07-02-2013 06:25 AM
Extract Numbers from Alphanumeric String	OTPM	Excel	6	05-13-2011 12:52 AM
Extract numbers from a text string	aleale97	Excel	4	02-10-2011 10:33 AM
Extract from String using Wildcard	whousedmy	Word	0	05-21-2009 01:35 AM