Text Manipulation Functions


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.

On the CD 

The companion CD-ROM contains a workbook named image from book text manipulation functions.xlsm that demonstrates all the functions in this section.

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:

?

Matches any single character

*

Matches zero or more characters

#

Matches any single digit (0–9)

[list]

Matches any single character in the list

[!list]

Matches any single character not in the list

Open table as spreadsheet

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.

Note 

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.

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) 

image from book
Figure 25-4: Examples of the SPELLDOLLARS function.

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

On the CD 

The SPELLDOLLARS function is too lengthy to list here, but you can view the complete listing in image from book spelldollars function.xlsm on the companion CD-ROM.




Excel 2007 Formulas
Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
ISBN: 0470044020
EAN: 2147483647
Year: 2007
Pages: 212

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net