Page 229 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Data with VBA

likely won’t be working through an Excel worksheet to find that substring. Instead, you might
have received rows of data that, through no fault of your own, were imported in Excel as a
single cell per row. What’s worse, the data fields aren’t of a fixed length, so you can’t use the
MID function without a bit of tweaking. However, even if you are ever unfortunate enough
to see data such as OI1800230IT7801CI486SPFX2D in a single worksheet cell, you can still
find a way to read it if you’re clever.
Rather than keep you in suspense, you should know that the data actually breaks out this
way: OrderID OI1800230, Item IT7801, CustomerID CI486, Shipping FedEx Second
Day. But how to you find that out? The method is equal parts cleverness and skill. The clev­
erness comes from marking the beginning of each field with a distinct code. In the example
string just shown, the first seven characters represent the OrderID, OI1800230. The OrderID
begins with the letters OI, which you can assume for the purposes of this example won’t
occur anywhere else in the string. The same marking technique is used to call out the Item
number (IT), the CustomerID (CI), and the Shipping method (SP).

Note You could guard against any stray occurrences of the marker sequences by putting
brackets around the marker, such as <OI> or <IT>. It’s the same sort of markup system
used in the Hypertext Markup Language (HTML) and the Extensible Markup Language Chapter 9
(XML), and it works well for other systems that don’t need to encode brackets as values.

When it comes to locating strings within other strings, having skill is operationally defined
as knowing about the SEARCH and FIND functions. Both functions return the number of
the character within a string at which a specific character or text string is found, but there are
minor but important differences in how the functions operate. Here are the functions’ syntaxes:

SEARCH(find_text, within_text, start_num)
FIND(find_text, within_text, start_num)

find_text is the text you want to find. If you use the SEARCH function, you can use the
wildcard characters, question mark (?) and asterisk (*), in find_text. A question mark
matches any single character, whereas an asterisk matches any sequence of characters. If you
want to find an actual question mark or asterisk, type a tilde (~) before the character. The
SEARCH function isn’t case-sensitive, so searching for e will also find E. The FIND function
is case-sensitive and doesn’t allow wildcards—you can think of it as a more precise version of
the SEARCH function. With FIND, searching for e will not find E.
● within_text is the text in which you want to search for find_text.
● start_num is the character number in within_text at which you want to start searching.
If you leave this parameter blank, the search will start from the beginning of the string
(that is, start_num = 1).

203
Part 4 Advanced VBA