Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Text strings can be manipulated with functions in a variety of ways, including reversing the display of a text string, scrambling the characters in a text string, or extracting specific characters from a text string. This section offers a number of function examples that manipulate text strings.

Reversing a String

The following REVERSETEXT function returns the text in a cell backward:

Function REVERSETEXT(text As String) As String ' Returns its argument, reversed REVERSETEXT = StrReverse(text) End Function

This function simply uses the VBA StrReverse function. The following formula, for example, returns tfosorciM:

=REVERSETEXT("Microsoft")

Scrambling Text

The following function returns the contents of its argument with the characters randomized. For example, using Microsoft as the argument may return oficMorts, or some other random permutation.

Function SCRAMBLE(text As Variant) As String ' Scrambles its string argument Dim TextLen As Long Dim i As Long Dim RandPos As Long Dim Temp As String Dim Char As String * 1 If TypeName(text) = "Range" Then Temp = text.Range("A1").text ElseIf IsArray(text) Then Temp = text(LBound(text)) Else Temp = text End If TextLen = Len(Temp) For i = 1 To TextLen Char = Mid(Temp, i, 1) RandPos = WorksheetFunction.RandBetween(1, Mid(Temp, i, 1) = Mid(Temp, RandPos, 1) Mid(Temp, RandPos, 1) = Char Next i SCRAMBLE = Temp End Function

This function loops through each character and then swaps it with another character in a randomly selected position.

You may be wondering about the use of Mid. Note that when Mid is used on the right side of an assignment statement, it is a function. However, when Mid is used on the left side of the assignment statement, it is a statement. Consult the Help system for more information about Mid.

Returning an Acronym

The ACRONYM function returns the first letter (in uppercase) of each word in its argument. For example, the following formula returns IBM:

=ACRONYM("International Business Machines")

The listing for the ACRONYM Function procedure follows:

Function ACRONYM(text As String) As String ' Returns an acronym for text Dim TextLen As Long Dim i As Long text = Application.Trim(text) TextLen = Len(text) ACRONYM = Left(text, 1) For i = 2 To TextLen If Mid(text, i, 1) = " " Then ACRONYM = ACRONYM & Mid(text, i + 1, 1) End If Next i ACRONYM = UCase(ACRONYM) End Function

This function uses the Excel TRIM function to remove any extra spaces from the argument. The first character in the argument is always the first character in the result. The For- Next loop examines each character. If the character is a space, the character after the space is appended to the result. Finally, the result converts to uppercase by using the VBA UCase function.

Does the Text Match a Pattern?

The following function returns TRUE if a string matches a pattern composed of text and wildcard characters. The ISLIKE function is remarkably simple, and is essentially a wrapper for the useful VBA Like operator.

Function ISLIKE(text As String, pattern As String) As Boolean ' Returns true if the first argument is like the second ISLIKE = text Like pattern End Function

The supported wildcard characters are as follows:

The following formula returns TRUE because the question mark (?) matches any single character. If the first argument were "Unit12", the function would return FALSE.

=ISLIKE("Unit1","Unit?")

The function also works with values. The following formula, for example, returns TRUE if cell A1 contains a value that begins with 1 and has exactly three numeric digits:

=ISLIKE(A1,"1##")

The following formula returns TRUE because the first argument is a single character contained in the list of characters specified in the second argument:

=ISLIKE("a","[aeiou]")

If the character list begins with an exclamation point (!),the comparison is made with characters not in the list. For example, the following formula returns TRUE because the first argument is a single character that does not appear in the second argument's list:

=ISLIKE("g","[!aeiou]")

To match one of the special characters from the table above, put that character in brackets. This formula returns TRUE because the pattern is looking for three consecutive question marks. The question marks in the pattern are in brackets so they no longer represent any single character:

=ISLIKE("???","[?][?][?]")

The Like operator is very versatile. For complete information about the VBA Like operator, consult the Help system.

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.

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 following formula returns the string One hundred twenty-three and 45/100 dollars:

=SPELLDOLLARS(123.45)

Figure 25-4 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)

Figure 25-4: Examples of the SPELLDOLLARS function.

Note that negative numbers are spelled out and enclosed in parentheses.