#1
|
|||
|
|||
Extracting certain text before footnote number
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 |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Extracting certain text before footnote number
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 |
#6
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Extracting certain text before footnote numbe
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 |