14,534
As the title of this article suggests these are Excel Worksheet functions for use in cell formula as opposed to VBA functions. Although some may also be available in VBA the are not as a rule. Where the function is available in VBA I will state it.
CLEAN()
The CLEAN function is used to "Clean" text. We've all had those situations when users copy a body of text from the web or some text document and paste it into a cell in Excel. The text often ends up interspersed with unprintable characters. This function can be used to remove all those characters.
The clean function removes ASCII characters 1-31, 129, 141, 143, 144, and 157. The only ones it doesn't remove are CHAR(127) and CHAR(160)
The syntax for the function is:
CLEAN ( Cell Reference of Text )In this first image you can see the formulas I used to test the function. In Cell Reference A1 I inserted ASCII Character 9 before a block of text and ASCII
Character 13 at the end of it. In Cell Reference A2 I entered the Clean Function and passed it the Value in A1. I then entered the Len() function in column B to evaluate the length of the text in the corresponding row of Column A.
In the below image you can see the values when I turn off the Formula display. As you can see the length of the Text in Column A decreases by 2 to indicate the removal of the two non-printing characters once I use the Clean function.
FIND()
The FIND function is used to search for the position of a substring in a body of text. One thing to note is that this function is case sensitive. Also if it doesn't find the substring it is searching for the function will return a #VALUE error. If you need to return a value such as 0 if the substring is not found you can use the ISERROR function to evaluate the result. I outline this function in more detail later in this Article.
The syntax for the function is:
FIND ( substring, Body of Text Reference, [start_position] )Note: start_position is optional. If no value is entered it defaults to 1, the first character of the Body of Text.
In the first image you can see the formulas entered in column B to search for the substring "the" in the text in the corresponding row in column A.
In the below image you can see the values when I turn off Formula display. The first one returns an error because the "the" in the sentence has a Capital letter T and as previously mentioned the function is case sensitive. The second row shows a value of 12 which is the position in the text that "the" starts and the third row again returns an error because it can't find the substring "the".
ISERROR()
(Can also be used in VBA, see Note on ISERR function)
The ISERROR function is used to discover if a cell contains an error value. It can be used to test to see if a formula returns an error value, a link is broken, etc. It tests for #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL. If any of these are found the function returns True otherwise it returns False.
The syntax for the function is:
ISERROR ( Value )A practical example of how to use this function can be demonstrated with an extension of the example used in the FIND function above.
In the first image you can see I used the ISERROR function in an IF statement in column C to evaluate the result of the FIND Function.
In the below image you can see if the FIND function in column B returns an error then column C shows a 0, otherwise it shows the value returned by the FIND function.
Note: ISERR() is a worksheet function that does the same thing but it cannot be used in VBA, whereas ISERROR() can be used in either.
Mary McCarthy
Bytes Admin
