Microsoft Office Tutorials and References

In Depth Information

If you specify a space as the Separator character, multiple spaces are treated as a

single space, which is almost always what you want. If n exceeds the number of ele-

ments in the string, the function returns an empty string.

The VBA code for the EXTRACTELEMENT function follows:

Function EXTRACTELEMENT(Txt As String, n As Long,

Separator As String) 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 the VBA 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 follow-

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

=SPELLDOLLARS(123.45)

Figure 26-5 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.