Microsoft Office Tutorials and References

In Depth Information

**Some Useful Worksheet Functions**

The VBA code for the
ExtractElement
function follows:

Function ExtractElement(Txt, n, Separator) As String

‘ Returns the nth element of a text string, where the

‘ elements are separated by a specified separator character

Dim AllElements As Variant

AllElements = Split(Txt, Separator)

ExtractElement = AllElements(n - 1)

End Function

This function uses VBA’s
Split
function, which returns a variant array that contains each

element of the text string. This array begins with
0
(not
1
), so using
n - 1
references the desired

element.

Spelling out a number

The
SPELLDOLLARS
function returns a number spelled out in text — as on a check. For example,

the following formula returns the string
One hundred twenty-three and 45/100 dollars:

=SPELLDOLLARS(123.45)

Figure 11-17 shows some additional examples of the
SPELLDOLLARS
function. Column C contains

formulas that use the function. For example, the formula in C1 is

=SPELLDOLLARS(A1)

Note that negative numbers are spelled out and enclosed in parentheses.

Figure 11-17:
Examples of the
SPELLDOLLARS
function.