Macro to search for and extract the first Capital letter of each word in a string.

Thanks so much for your help with this earlier! This time I would like to do a little more with this macro. You showed me how to add an auto number, but I also want to add the first capital  letter of every word on the line.


Here is an example of how the data looks originally:

    1 Class in COER 112
       Label "Microcomputer Operating Systems";
    1 Class in COER 116
      Label "Microcomputer Hardware";
    1 Class in COER 125
      Label "Seminar";
    1 Class in NETW 170
      Label "Intro to Information Security";

What I basically want to do is find the string (Label ") and replace it with (Label  X "), where the "X is an incremental counter. THEN take the first CAPITAL letter of each word that follows until you run into the ";" character, or End of Line.
 
Therefore, the end result should look like this:

    1 Class in COER 112
      Label 1MOS "Microcomputer Operating Systems";
    1 Class in COER 116
      Label 2MH "Microcomputer Hardware";
    1 Class in COER 125
      Label 3S "Seminar";
    1 Class in NETW 170
      Label 7IIS "Intro to Information Security";


Thanks to the awesome amount of help given by Andreas Killer, below, I've listed the code down that only finds the "Label" string, then adds the counter.  What I'm looking to do now is add the functionality to extract the first Capital letter of every word that follows the "label string all the way to the end of the line, OR until it runs into the ";" character, whichever is easier.


Sub FindReplace(ByVal FName As String)
  Dim D As Document
  Dim R As Range
  Dim i As Integer
  'Open the document
  Set D = Documents.Open(FName)
  'Get the range for the whole document
  Set R = D.Content
 
  'Search for the keyword
  Do While R.Find.Execute("Label """)
    'Note: Find.Execute modifies the object R to the location of the keyword!
    'Note: InsertAfter expand the object R to include the text to be inserted!
    'Find the " and replace it with null
    'R.Find.Execute (Replace("Label " & Chr(34), Chr(34), ""))
    R.Find.Execute (Replace("Label """, """", ""))

    'Insert the counter after the keyword
    i = i + 1
    R.InsertAfter i & " "
       
    'Setup object R to the position after the keyword up to the end of the document
    R.SetRange R.End, D.Content.End
  Loop
 
  'Save and close
  D.Close True, wdOriginalDocumentFormat

End Sub

 


Any ideas on the code to create the additional functionality?

Answer
Answer

Use:

Dim i As Long, j As Long
Dim rng1 As range, rng2 As range
Dim str As String
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
    i = 1
    Do While .Execute(FindText:="Label", Forward:=True, _
    MatchWildcards:=False, Wrap:=wdFindStop) = True
        Set rng1 = Selection.range
        Set rng2 = Selection.range.Paragraphs(1).range
        Selection.Collapse wdCollapseEnd
        Selection.Move wdParagraph, 1
        rng2.Start = rng1.End + 2
        rng2.End = rng2.End - 2
        str = ""
        For j = 1 To rng2.Characters.count
            If Asc(rng2.Characters(j)) = 40 Then
                Exit For
            ElseIf Asc(rng2.Characters(j)) > 64 And Asc(rng2.Characters(j)) < 91 Then
                str = str & rng2.Characters(j)
            End If
        Next j
        rng1.text = rng1.text & " " & i & str
        i = i + 1
    Loop
End With

Hope this helps,
Doug Robbins - MVP Office Apps & Services (Word)
dougrobbinsmvp@gmail.com
It's time to replace ‘Diversity, Equity & Inclusion’ with ‘Excellence, Opportunity & Civility’ - V Ramaswamy

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated October 5, 2021 Views 411 Applies to: