Microsoft Office Tutorials and References
In Depth Information
Text Manipulation Functions
The following formula returns TRUE if cell A1 contains a text string or if the cell is formatted as Text:
=CELLHASTEXT(A1)
Extracting the nth Element from a String
The EXTRACTELEMENT function is a custom worksheet function that extracts an element from a
text string based on a specified separator character. Assume that cell A1 contains the following text:
123-456-789-9133-8844
For example, the following formula returns the string 9133, which is the fourth element in the
string. The string uses a hyphen (-) as the separator.
=EXTRACTELEMENT(A1,4,”-”)
The EXTRACTELEMENT function uses three arguments:
h t xt : The text string from which you’re extracting. This can be a literal string or a cell
reference.
h n : An integer that represents the element to extract.
h separator : A single character used as the separator.
If you specify a space as the Separator character, multiple spaces are treated as a
single space (almost always what you want). If n exceeds the number of elements 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.
 
Search JabSto ::




Custom Search