#1
|
|||
|
|||
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! |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
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! |
#4
|
||||
|
||||
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 |
#5
|
||||
|
||||
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 |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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! |
#8
|
||||
|
||||
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#9
|
||||
|
||||
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 |
#10
|
|||
|
|||
You guys are AWESOME! I'll give these a try tomorrow when I get back to the office.
|
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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!
|
#13
|
||||
|
||||
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 |
#14
|
|||
|
|||
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. |
#15
|
||||
|
||||
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 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 |
Tags |
numeric, phonetic, word macros |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating your own Section Headings using Numbers and letters and linking to the TOC | rehahm | Word | 4 | 10-07-2015 10:20 AM |
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 |
Captions mixing letters and numbers | trew | Word | 7 | 11-21-2012 12:54 AM |
Creating TOC with letters and numbers | tanababa | Word | 1 | 04-28-2011 01:35 AM |