Word VBA: Code to search for text like "AB ######-##" and hyperlink them to Custom URL

So... I have a ton of word documents with a ton of references in formats like
"AB ######-##"
or
"BC ######-##"
or
"LMN ######-##"
or
(etc., without the quotes, where # is always a number)

...and I need to have Word automatically find them all and hyperlink them (we'll pretend that we need to Google or Yahoo search them). However, here are the conditions:
1. The hyperlink URL cannot include the -. A Replace function to remove these (but leave them in the word document text and leave the link as it originally appeared as text) should be sufficient.
2. Only the code numbers actually get searched. The hyperlink URL prefix and suffix need to be selected (Some sort of select...case) depending on the (two, three, or four)-letter "AB", "BC", "LMN" in front of the number.
3. If the number is not of length 10, add zeros on the left to make it of length 10.

I'm found this code to start things rolling, but I've got no clue what's going on with the findText...
http://www.eggheadcafe.com/software/aspnet/29982909/finding-and-converting-te.aspx
LVL 1
travisjbennettAsked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
<[A-Z]{2,4} [0-9\-]{2,13}
0
 
travisjbennettAuthor Commented:
I'd be happy to award points for any part of the solution... ESPECIALLY finding these tags and hyperlinking them. The rest I can implement with the replace and select case stuff myself.
0
 
aikimarkCommented:
hyperlink them to or from what?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
aikimarkCommented:
these would be your findtext method arguments
AB ^#^#^#^#^#^#-^#^#
BC ^#^#^#^#^#^#-^#^#
LMN ^#^#^#^#^#^#-^#^#

Open in new window

0
 
travisjbennettAuthor Commented:
Hyperlink AB 123456 to a Google search for now: http://www.google.com/search?hl=en&source=hp&q=00123456&aq=f&aqi=&aql=&oq=&gs_rfai=
Hyperlink BC 123456-78 to a Yahoo search for now: http://search.yahoo.com/search;_ylt=AtT.NonbAMlJ2FKVsHcQKUqbvZx4?p=12345678&toggle=1&cop=mss&ei=UTF-8&fr=yfp-t-701

I can't post the links to what they will actually be hyperlinked to... but that shouldn't matter at all.
0
 
travisjbennettAuthor Commented:
Is there a way to search for any 2-4 characters from A to z followed by a space followed by any 4-10 numbers with or without a hyphen in the middle of the numbers?
0
 
aikimarkConnect With a Mentor Commented:
You might need to issue two FIND methods for each document, one with a hyphen and one without a hyphen.

Here are the two patterns.  They must include the Wildcard = true indicator.
<[A-Z]{2,4} [0-9]{4,8}-[0-9]{2}

<[A-Z]{2,4} [0-9]{4,10}

Open in new window

0
 
travisjbennettAuthor Commented:
Good idea. Sometimes it might end with a - then just one digit, like 121212-5, so I'm going to try
"<[A-Z]{2,4}>[ ][0123456789-]{4,10}"

I think I've got some of it working OK for now, I'll let you know when I get stumped.
0
 
aikimarkConnect With a Mentor Commented:
* you shouldn't use the > in your pattern
* you don't need to use the [] around the space character
* you will get better performance with [0-9] than [0123456789]
* the hyphen character inside the [] in your pattern isn't correct

<[A-Z]{2,4} [0-9]{4,8}-[0-9]{1,2}

<[A-Z]{2,4} [0-9]{4,10}

Open in new window

0
 
travisjbennettAuthor Commented:
How can I search for any combination of digits and/or hypens of ALL types, from length 2 to 13?
0
 
travisjbennettAuthor Commented:
Aikimark,
Last question while I can still pick your brain: Between the text and the serial regions, how would I require any one (and only one) of the following:
* One space " "
* One hyphen of any type "-"
* One colon followed by one space ": "
?
0
 
aikimarkCommented:
can you post some examples of matching and non-matching strings?

The last pattern I posted matches 2-4 letters followed by a space followed by 2-13 digit or hyphen characters.
0
 
travisjbennettAuthor Commented:
OK     AB 123456
OK     AB 0000123456
OK     AB 123456-78
OK     AB-123456
OK     AB-123456-78
OK     AB: 123456
OK     AB: 123456-78
BAD  AB  123456 (2 Spaces)
BAD  AB:  123456 (2 Spaces again)
BAD  AB- 123456 (Spaces around hyphen)
The [A-Z]{2,4} and [0-9\-]{2,13} parts should remain the same. I need something in between to hand the
requirement of any one (and only one) of the following:
* One space " "
* One hyphen of any type "-"
* One colon followed by one space ": "
0
 
aikimarkConnect With a Mentor Commented:
AB-123456-78
has two hyphens

I don't think one single pattern will work, but you might try
<[A-Z]{2,4}[ \-][0-9][0-9\-]{1,12}
<[A-Z]{2,4}: [0-9][0-9\-]{1,12}

Of course, such a pattern would allow multiple hyphens in the right hand part.
0
 
travisjbennettAuthor Commented:
Ok, I think that's all the info I need for now. Thank you!
I'll post what patterns I end up using soon.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.