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)
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:
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
Note that negative numbers are spelled out and enclosed in parentheses.