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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
aikimarkCommented:
these would be your findtext method arguments
AB ^#^#^#^#^#^#-^#^#
BC ^#^#^#^#^#^#-^#^#
LMN ^#^#^#^#^#^#-^#^#

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
aikimarkCommented:
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
aikimarkCommented:
* 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
aikimarkCommented:
<[A-Z]{2,4} [0-9\-]{2,13}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
aikimarkCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.