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.
 
Search JabSto ::




Custom Search