Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-06-2020, 04:20 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 Help creating a macro to identify values that have letters and numbers.

I have been trying to create a macro that is WAY more complex than anything I have ever made so I’m reaching out for some help. I am trying to see if it is possible to automate the task of scanning through a multi-page word document and highlighting or identifying any instance where a phonetic and numeric value is combined. (e.g. ”ABC12345” or “123ABC456”)

So that is all I was trying to do, but since I am already asking for help maybe someone with more knowledge then myself can help me a little more. If what I asked for above is possible it would help me even more if it could also create a list (maybe on a different file or page) of all the items it found (e.g. A1234, B1234, C1234, D1234……. OR listed as a column). I do not want it to delete them just provide a list of what it found.

I know I’m asking for a lot and I honestly don’t even know if Word is possible of doing this, but it doesn’t hurt to ask. Anyway thanks for even taking the time to read this, any help or direction is greatly appreciated. Thanks!
Reply With Quote
  #2  
Old 08-06-2020, 05:53 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

What are the rules for a valid hit? How many digit/characters need to be included for it to be a hit?
For instance which of the following are hits
a123 a1 A1 1a 1A TF2 A12 AB12 ABC1 abc123

A 'not completely unrelated' question with code was looked at on this thread End With without With - except there is a With - Solved - Eileen's Lounge after being asked on this forum. If you study the code shown in that thread you can have a go at adapting that as your first steps. There is likely to be a faster running solution based on the answer you provide for the above questions but that code could give you enough clues to get you started.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 08-06-2020, 06:45 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

Technically all the examples are hits because the documents I'm reviewing have references to equipment but every piece of equipment has a different abbreviation based on type (e.g. Valve=V, Motor=MTR....) and different numbers based on location and how many there are in the field (e.g. Area 82, Valve#123 = 82V123 OR Motor#12=MTR12). I know it's kind of vague but some could be 4 characters (with 2 letters and 2 numbers) and others can be 10 characters (with any combination of numbers and letters). Again I know it's not a simple request so I will take anything I can get.

Honestly even if I got the macro to highlight everything, I would still go through the document to confirm that what it identified should be highlighted. That is why the extra part in my request would help me a little more, because if I had a separate list I could review what was highlighted and not have to scroll through the pages. Then I could verify based on that list and if that list is in a different location and I have confirmed everything, I can just delete the list and save the original file with the highlighted equipment numbers.

Please let me know if you have any more questions or if I need to clarify something.

Also I haven't looked at the other forum you mentioned yet but I will definitely check it out and see if I can tweak it to fit my needs. Thank you!
Reply With Quote
  #4  
Old 08-06-2020, 07:36 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

Can we exclude lowercase letters and anything with less than 4 characters?

Do you want the 'words' highlighted AND a separate doc?
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #5  
Old 08-06-2020, 07:49 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

I'm not a fan of creating a macro involved in the drudgery of testing every word so I'm thinking of 'out of the box' solutions to see if there is a clever solution to this problem.

One option I've thought of is to make use of the spell checker with the option to Ignore words that contain numbers turned off. This will give you a red fuzzy underline on all of the codes you appear to be searching for. Plus, we don't even need a macro to review all of these because you can just use the Spell Check to progress through them.

A macro to harvest these 'spelling mistakes' into another file should be easy enough and run faster than examining every word in the doc. You might want to exclude misspelled words that don't contain a number but that is not too difficult.

I've also considered tagging the hits as either a TOC entry or and Index entry so you have an in-document way of listing and navigating to the instances.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #6  
Old 08-06-2020, 08:14 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

I think we should keep the lowercase because some people make typos and might forget to capitalize the equipment numbers. If that makes it more difficult to write the code we can take out the lower case.

The range of the characters will most likely be between 4 to 12 digits.

Also yes, if possible. I'd like the equipment numbers highlighted in the word file and also displayed in a list format somewhere separate from the original document or as a final page that I can remove when done. This would be a duplicate list of what was highlight by the first part of the macro. This is just so I can verify what was highlighted and know if it caught any mistakes because it is such a wide search parameter.
Reply With Quote
  #7  
Old 08-06-2020, 08:30 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

I get it, that's why I was having such a hard time trying to figure this out. There are so many variable in the search I need to do to identify these damn numbers. In excel I think I would have a chance, even in AutoCAD I might figure this out, but in Word the scope is too wide to isolate a couple characters with with numbers and letters. I was even looking into third party software for help with this, cause doing it by hand is insanely tedious. But I didn't expect it to be easy to figure this out. The highlight part is really the only part I need the rest was really just a quality of life feature.

Also the spell checker is a really good idea!
Reply With Quote
  #8  
Old 08-06-2020, 09:30 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

Just going on the highlighting part, the code is very simple
Code:
Sub HilitePartNums()
  Dim rngWord As Range, aRng As Range, sWord As String
  For Each rngWord In ActiveDocument.Words
    sWord = UCase(Trim(rngWord.Text))
    If sWord Like "*[0-9]*" Then
      If sWord Like "*[A-Z]*" Then
        rngWord.HighlightColorIndex = wdYellow
        Debug.Print sWord
      End If
    End If
  Next rngWord
End Sub
This will highlight the instances and add a compiled list in your Immediate window. I think there is a limit of how many lines can sit in the Immediate window so you will not see the early ones if you have over 255? hits.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #9  
Old 08-06-2020, 09:31 PM
gmayor's Avatar
gmayor gmayor 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: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

You could use search to find numbers then expand to the word and use RegEx to see of the word has letters e.g. the following will find all the examples in this thread if they are in the main document text range



Code:
Sub Macro1()
Dim oRng As Range, oNum As Range
    Set oRng = ActiveDocument.Range
    With oRng.Find
        Do While .Execute(findText:="[0-9]{1,}", MatchWildcards:=True)
            Set oNum = oRng.Words(1)
            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
Dim objMatch As Object
Dim colMatches As Object

    Set objRegExp = CreateObject("VBScript.RegExp")
    objRegExp.Pattern = strFind
    objRegExp.IgnoreCase = True
    objRegExp.Global = True
    If (objRegExp.Test(strText) = True) Then
        Set colMatches = objRegExp.Execute(strText)
        For Each objMatch In colMatches
            TestRegExp = True
            Exit For
        Next
    End If
    Set objRegExp = Nothing
    Set colMatches = Nothing
    Set objMatch = Nothing
End Function
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #10  
Old 08-06-2020, 09:41 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

You guys are AWESOME! I'll give these a try tomorrow when I get back to the office.
Reply With Quote
  #11  
Old 08-07-2020, 08:30 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

Graham,


What is/was your intent with the parts of your function that I've stetted out below:

Code:
Function TestRegExp(strFind As String, strText As String) As Boolean
Dim objRegExp As Object
'Dim objMatch As Object
'Dim colMatches As Object
    TestRegExp = False
    Set objRegExp = CreateObject("VBScript.RegExp")
    objRegExp.Pattern = strFind
    objRegExp.IgnoreCase = True
    objRegExp.Global = True
    If (objRegExp.Test(strText) = True) Then TestRegExp = True
'        Set colMatches = objRegExp.Execute(strText)
'        For Each objMatch In colMatches
'            TestRegExp = True
'            Exit For
'        Next
'    End If
    Set objRegExp = Nothing
'    Set colMatches = Nothing
'    Set objMatch = Nothing
End Function
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #12  
Old 08-07-2020, 09:14 AM
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

Andrew, the highlight worked perfectly I'm so happy!!! I couldn't quite figure out the list part though. Thank you and everyone who helped!
Reply With Quote
  #13  
Old 08-07-2020, 08:59 PM
gmayor's Avatar
gmayor gmayor 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: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Quote:
Originally Posted by gmaxey View Post
Graham,
What is/was your intent with the parts of your function that I've stetted out below:
It was part of a more comprehensive function and should have been deleted as it is not required here, though it wouldn't stop the function from working.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #14  
Old 08-10-2020, 03:02 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

You've already helped me out so much and I was able to find a way to extract the text to another document, but can I ask for one more thing?

Some of the numbers have facility codes that are attached to the equipment numbers with a hyphen (e.g. N50-V12345). Can the macro you created include the hyphen when capturing the text? At the moment it will get the N50 and the V12345 as seperate highlights not including the -.

If this can't be done, it really is no problem. I'm already super happy about all the help I've already gotten.
Reply With Quote
  #15  
Old 08-10-2020, 04:36 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

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
__________________
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 08:53 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