Extracting certain text before footnote number

Taisir · #1 11-25-2014, 03:18 AM

Dear All,
I have large number of documents (800 plus) exported from a database that are all in the same format.

Every document contains Names of authors of large research projects. All names are written in the same format, Last Name, (Comma) First Initials (in Hypertext) followed by the same name within parenthesis in regular text along with a footnote designating the author affiliated organization. All name listed in alphabetical order. Please see attached sample.

I need a VBA (Macro) that can pull the names associated with certain footnote number and place them at the bottom of the document. For example for organization associated with the number “34”, the desired output will be:

Aefsky, S
Amelung, C
Bensinger, J. R
Bianchini, L
Blocker, C

So whenever I need to pull the name of certain organization, all what I need to do is change the footnote number in the VBA and run it again.

Many thanks in advance for any help

All the best Taisir

macropod · #2 11-25-2014, 05:56 PM

Try:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim i As Long, Str As String
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "\(*\)"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .MatchSoundsLike = False
    .Execute
  End With
  Do While .Find.Found
    i = i + 1
    Str = Str & " " & .Text
    .Find.Execute
  Loop
  .Start = ActiveDocument.Range.Start
  .End = ActiveDocument.Range.End
  .Find.Execute Replace:=wdReplaceAll
  .InsertAfter vbCr
  .Paragraphs.Last.Style = wdStyleNormal
  Str = Replace(Trim(Str), ") (", Chr(11))
  Str = Mid(Str, 2, Len(Str) - 2)
  .InsertAfter Str
End With
Application.ScreenUpdating = True
MsgBox i & " records processed."
End Sub

Taisir · #3 11-25-2014, 08:33 PM

Dear Paul,
Many thanks for the Macro.

It seems to me that the Macro you provided extracted all the name of the authors from the file.

However, what I need is how to extract the names associated with specific footnote such as "34" to produce only the names with organization designated with the footnote number "34". Plz refer to the example in my original post

Ideally, I need to be able to adjust the macro with another footnote number whenever I need to pull the names for that organization.

Many thanks again and all the best. Taisir

macropod · #4 11-25-2014, 09:28 PM

The problem is there are NO footnotes in your document, so it's by no means clear what you want. For example, the first string is:
Aad, G (Aad, G.)[ 75 ]
(can't do superscripts here)
• the 'Aad, G' contains a hyperlink to a web address:
http://apps.webofknowledge.com/OneCl...value=Aad,%20G
• the (Aad, G.) is just text
• the [ 75 ] contains a hyperlink to another web address:
http://apps.webofknowledge.com/full_...J&page=1&doc=1
Not only that, but accessing these web addresses requires a secure login.

Taisir · #5 11-25-2014, 11:05 PM

Hello Again Paul,

Maybe, I am using the term "Footnote" incorrectly, It refers to the text in brackets that indicate the organization(s) that the researcher belongs to.

Your VBA looks great. I would appreciated greatly if you can adjust it to be able to run it for a specific "number within the brackets" to produce the names associated with that number as indicated in my first post.

I hope that help.

Many thanks again Taisir

macropod · #6 11-25-2014, 11:19 PM

That requires a rather different approach. Try the following macro. It converts your document to a table, which it then sorts by reference #. That way, all the references are gouped together. There do seem to be some anomalies/inconsistencies with your data, in that you sometimes have multiple names without references, followed by one or more references in a row, but not always the same number of references as names.

The output table sorts all reference series by the first reference in that series. It seems to produce consistent results.

Code:

Sub Demo()
Application.ScreenUpdating = False
With ActiveDocument
  With .Range
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchWildcards = True
      .Text = "\["
      .Replacement.Text = "^t"
      .Execute Replace:=wdReplaceAll
      .Text = "\]*[\;^13]"
      .Replacement.Text = "^p"
      .Execute Replace:=wdReplaceAll
      .Text = "\(*\)"
      .Replacement.Text = ""
      .Execute Replace:=wdReplaceAll
      .Text = "^s"
      .Replacement.Text = ""
      .Execute Replace:=wdReplaceAll
      .Text = ","
      .Wrap = wdFindStop
      .Execute
    End With
    Do While .Find.Found
      If .Hyperlinks.Count = 0 Then .Text = vbTab
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
  End With
  With .Range
    .ConvertToTable
    With .Tables(1)
      .Columns.Width = 36
      .Columns(1).Width = 144
      .Sort ExcludeHeader:=False, CaseSensitive:=False, LanguageID:=wdEnglishUS, _
        FieldNumber:="Column 2", SortFieldType:=wdSortFieldNumeric, SortOrder:=wdSortOrderAscending, _
        FieldNumber2:="Column 1", SortFieldType2:=wdSortFieldAlphanumeric, SortOrder2:=wdSortOrderAscending
    End With
  End With
End With
Application.ScreenUpdating = True
End Sub

Because your document is full of hyperlinks, doing any kind of processing that has to consider their display text (as would be required if you wanted to extract only those names associated with reference #34, for example) would be much more complicated than working with simple text.

Taisir · #7 11-26-2014, 12:27 AM

Dear Paul,
Many thanks again for the prmpt reply.
Your mew VBA solution is great and provided me with many other benefits that I can use.

I really appreciate your help.

All the best

Next I will try to mark this inquiry as SOLVED.
Cjeers

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Heading's Footnote's number is displayed in TOC. How to remove the number in TOC?	Orehrepus	Word	1	07-13-2014 12:51 PM
VBA code to read number of footnote and enter in text	rekent	Word VBA	2	05-13-2014 06:53 AM
Extracting a phone number from a string that contains text and numbers.	hommi16	Excel	2	06-05-2013 09:19 PM
number in footnote line	ScientificKat	Word	2	07-09-2012 06:25 AM
Footnote text is not aligned with the corresponding number	Patrick1988	Word	0	08-28-2010 09:33 PM