Microsoft Office Forums - View Single Post - Help creating a macro to identify values that have letters and numbers.

Guessed · 08-10-2020, 04:36 PM

Hmm, it can be done but requires a more involved approach built off Graham's search method. This is my first attempt at it.

Code:

Sub HilitePartNumsHyph()
  Dim rngWord As Range, aRng As Range, sWord As String
  Dim oRng As Range, oNum As Range, rngFrag As Range
  
  Set oRng = ActiveDocument.Range
  With oRng.Find
    Do While .Execute(findText:="[0-9]{1,}", MatchWildcards:=True)
      Set oNum = oRng.Words(1)
      'oNum.Select                                  'XXXX Testing Only XXX
      sWord = UCase(Trim(oNum.Text))
      If sWord Like "*[A-Z]*" Then                  'If there is a letter included it is a part num
        Set rngFrag = oNum.Words.Last.Next
        'rngFrag.Select                             'XXXX Testing Only XXX
        If rngFrag.Text = "-" Then
          rngFrag.MoveEnd Unit:=wdWord, Count:=1
          oNum.End = rngFrag.End
          oNum.Select                               'XXXX Testing Only XXX
        End If
        oNum.HighlightColorIndex = wdYellow
        Debug.Print oNum                            'This is the output list
      End If
      oRng.Start = oNum.End
    Loop
  End With
End Sub

The behaviour is such that it will only find a compound part number (one with a hyphen) if the first fragment contains both a number and a letter
It doesn't care what comes after the hyphen, as long as Word considers it to be the 'next word'. This means it wont find N50-V12345-N51 as a SINGLE item, nor will it find 123-NNN or NNN-123. It will find N50-- and N50-8908098