423,846 Members | 2,048 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How to use the CLEAN, FIND and ISERROR Worksheet Functions in Excel

MMcCarthy
Expert Mod 10K+
P: 14,534
There are so many functions available in Excel Worksheets that users often stick with the most common ones until they start exploring trying to find a solution to a problem. Sometimes it doesn't even occur to them that there may be a function to deal with the issue. So I decided to draw on my experience to note some of the lessor known functions I have found to be very useful.

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
Attached Images
File Type: jpg FormulasClean.jpg (64.6 KB, 1354 views)
File Type: jpg ValuesIsError.jpg (72.3 KB, 1658 views)
File Type: jpg ValuesFind.jpg (71.4 KB, 1201 views)
File Type: jpg FormulasIsError.jpg (80.0 KB, 1188 views)
File Type: jpg FormulasFind.jpg (74.2 KB, 1434 views)
File Type: jpg ValuesClean.jpg (63.2 KB, 1459 views)
May 4 '13 #1
Share this Article
Share on Google+
2 Comments


NeoPa
Expert Mod 15k+
P: 31,121
SEARCH() is similar to FIND() but not case-sensitive.
SUBSTITUTE() is one of my favourites. Very similar to the VBA function Replace(), which is quite different from the worksheet function REPLACE().
May 10 '13 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
I removed the last paragraph and changed the thread title to make it a better search result. I'll look at doing another thread with SEARCH, SUBSTITUTE and REPLACE.
May 13 '13 #3