Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 08-10-2020, 05:11 PM
Mr J Mr J is offline Help creating a macro to identify values that have letters and numbers. Windows 10 Help creating a macro to identify values that have letters and numbers. Office 2016
Novice
Help creating a macro to identify values that have letters and numbers.
 
Join Date: Aug 2020
Location: California, US
Posts: 15
Mr J is on a distinguished road
Default

That did it! Andrew you are awesome! Graham I didn't forget about you either, I added to both the reputations on all the comments for you guys. Thanks!
Reply With Quote
  #17  
Old 08-11-2020, 07:17 AM
gmaxey gmaxey is offline Help creating a macro to identify values that have letters and numbers. Windows 10 Help creating a macro to identify values that have letters and numbers. Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,428
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Andrew,


Not fully flushed out, but this seems to capture the ones yours missed:

Code:
Sub FlagAlpaNumericWords()
Dim oRng As Range, oNum As Range
Dim bCompound As Boolean
Dim lngStart As Long
  Set oRng = ActiveDocument.Range
  With oRng.Find
    Do While .Execute(findText:="[0-9]{1,}", MatchWildcards:=True)
      bCompound = False
      Set oNum = oRng.Words(1)
      lngStart = oNum.Start
      Do While oRng.Characters.First.Previous = "-"
        oRng.MoveStart wdCharacter, -1
        oRng.MoveStart wdWord, -1
        oNum.Start = oRng.Start
        bCompound = True
      Loop
      Do While oRng.Characters.Last.Next = "-"
        oRng.MoveEnd wdCharacter, 1
        oRng.MoveEnd wdWord, 1
        bCompound = True
      Loop
      If bCompound Then
        Set oNum = oRng
        If oNum.Start > lngStart Then oNum.Start = lngStart
        oNum.Select
      Else
        oNum.End = oNum.End - 1
      End If
      If TestRegExp("[A-Z]", oNum.Text) = True Then
        oNum.HighlightColorIndex = wdYellow
      End If
      oRng.Collapse 0
    Loop
  End With
End Sub

Function TestRegExp(strFind As String, strText As String) As Boolean
Dim objRegExp As Object
  Set objRegExp = CreateObject("VBScript.RegExp")
  objRegExp.Pattern = strFind
  objRegExp.IgnoreCase = True
  objRegExp.Global = True
  TestRegExp = objRegExp.Test(strText)
  Set objRegExp = Nothing
End Function
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/

Last edited by gmaxey; 08-11-2020 at 04:07 PM.
Reply With Quote
  #18  
Old 08-11-2020, 10:56 PM
Guessed's Avatar
Guessed Guessed is offline Help creating a macro to identify values that have letters and numbers. Windows 10 Help creating a macro to identify values that have letters and numbers. Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,969
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Greg, that looks like a better approach. I had a go at minimising the code and avoiding the RegEx which I don't think adds any value to this particular problem.

Code:
Sub HilitePartNumsHyph()
  Dim rngWord As Range, aRng As Range, sWord As String
  Dim oRng As Range, oNum As Range, rngFrag As Range, sPref As String
  
  Set oRng = ActiveDocument.Range
  With oRng.Find
    Do While .Execute(findText:="[0-9]{1,}", MatchWildcards:=True)
      Set oNum = oRng.Words(1)
      sWord = UCase(Trim(oNum.Text))
      If sWord Like "*[A-Z]*" Then                  'If there is a letter included it is a part num
        sPref = Ucase(oNum.Characters.First.Previous)
        Do While SwitchHitter(sPref)     'Look back
          oNum.MoveStart Unit:=wdWord, Count:=-1
        Loop
        If oNum.Text = Trim(oNum.Text) Then   'not ending with a space
          Do While SwitchHitter(UCase(oNum.Characters.Last.Next))   'Look forward
            oNum.MoveEnd Unit:=wdWord, Count:=1
          Loop
        End If
        oNum.HighlightColorIndex = wdYellow
        Debug.Print oNum                            'This is the output list
      End If
      oRng.Start = oNum.End
    Loop
  End With
End Sub

Function SwitchHitter(aChar As String, Optional str As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-") As Boolean
  'Returns true if the character appears in provided string
  SwitchHitter = InStr(str, aChar) > 0
End Function
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #19  
Old 08-12-2020, 11:59 AM
gmaxey gmaxey is offline Help creating a macro to identify values that have letters and numbers. Windows 10 Help creating a macro to identify values that have letters and numbers. Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,428
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Andrew,

I ran your code with the following result. I don't know if that is your intent or not. With the code I posted yesterday. All those pointed out will be flagged.
Attached Images
File Type: jpg Test Run.jpg (28.9 KB, 10 views)
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #20  
Old 08-12-2020, 03:57 PM
Guessed's Avatar
Guessed Guessed is offline Help creating a macro to identify values that have letters and numbers. Windows 10 Help creating a macro to identify values that have letters and numbers. Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,969
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Hi Greg

I've stuffed it up if 'test' and 'SINGLE item' got highlighted. That was not the intent.

Based on what the OP asked for, I was coding it to only start looking either way if it found a 'word' with at least one digit and letter. If it happens to have a dash at either end then it should extend the selection if the other side of the dash is letters and/or digits.

On those parameters, I would expect to intentionally exclude NNN-123 but catch NN1-123 and NNN-1N2. I would also expect to get most of N1000-2.2 but exclude the other side of the decimal (.2).

I think the code options are all available for the OP so he can do testing and work out exactly what he wants in and out.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Tags
numeric, phonetic, word macros



Similar Threads
Thread Thread Starter Forum Replies Last Post
Help creating a macro to identify values that have letters and numbers. Creating your own Section Headings using Numbers and letters and linking to the TOC rehahm Word 4 10-07-2015 10:20 AM
Help creating a macro to identify values that have letters and numbers. Creating a TOC with sequential letters instead of page numbers at the right margin shansen Word 1 03-11-2014 12:43 PM
Look up cell values with mixed number and letters Cardinal2 Excel 1 02-10-2013 01:40 AM
Help creating a macro to identify values that have letters and numbers. Captions mixing letters and numbers trew Word 7 11-21-2012 12:54 AM
Help creating a macro to identify values that have letters and numbers. Creating TOC with letters and numbers tanababa Word 1 04-28-2011 01:35 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:22 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft