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,421
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,932
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,421
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,932
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

Thread Tools
Display Modes


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 05:43 AM.


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