Expand your text string capabilities with two additional VBA functions

Expand your text string capabilities with two additional VBA functions

by Stephen Nebel

Application:

Microsoft Excel 2000/2002/2003/2007

Operating System:

Microsoft Windows

We describe several VBA functions that you can use to work with strings in the article titled “Three ways to manipulate strings without retyping your data,” found in this issue. Another useful but somewhat obscure function for string manipulation in VBA is Split(). We’ll take a look at it here because it may provide you with some additional ideas for string manipulations.

Split() takes two parameters:

1.          A string.

2.          An optional delimiter character, with the space being the default.

Split() returns a reference to a single dimension array containing the character clusters of the string per the delimiter.

For example, Split(“Intro to Six Sigma 0.5 PDUs”) would return a reference to an array containing the following character clusters: Intro, to, Six, Sigma, 0.5, and PDUs. The array is zero-based. All you have to do is access the second-to-last element and you’ve got your PDU value. Then you just concatenate the elements before that to get your course name. If you are concatenating all elements, you can use VBA’s JOIN()function to shortcut the process.

For an example of <[...]