Microsoft Office Tutorials and References
In Depth Information
Does a cell contain text?
A number of Excel's worksheet functions are at times unreliable when dealing with text in a cell. For example,
the ISTEXT function returns FALSE if its argument is a number that's formatted as Text. The following
CELLHASTEXT function returns TRUE if the cell argument contains text or contains a value formatted as
Text:
Function CELLHASTEXT(cell As Range) As Boolean
‘ Returns TRUE if cell contains a string
‘ or cell is formatted as Text
Dim UpperLeft as Range
CELLHASTEXT = False
Set UpperLeft = cell.Range(“A1”)
If UpperLeft.NumberFormat = “@” Then
CELLHASTEXT = True
Exit Function
End If
If Not IsNumeric(UpperLeft.Value) Then
CELLHASTEXT = True
Exit Function
End If
End Function
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:
• txt: The text string from which you're extracting. This can be a literal string or a cell reference.
• n: An integer that represents the element to extract.
• separator: A single character used as the separator.
Search JabSto ::




Custom Search