Essential Functions in Excel for Data Preprocessing

Microsoft Excel is a great tool for preprocessing and handling structured data. Excel has functions and techniques which makes it easier to clean structured data. We’ll discuss a few of the many functions along with a few examples. Before proceeding further, we’ll discuss a few basic functions which will be a part of a larger formulae later in this article.

Basic Functions

1.?? IF

This function checks a condition and returns a specified value accordingly. In the example below, the function checks a condition “is 2 greater than 3”. If the condition is TRUE (which it isn’t) then the function returns “2 > 3” else it return “2 < 3”.

=IF(2>3,"2 > 3","2 < 3")

Since, 2 isn’t greater than 3, so the condition is FALSE and the value returned will be “2 < 3”.

2.?IFERROR

This function helps in error handling. In Excel, the most common errors are #N/A, #VALUE!, #DIV/0!, #REF!, #NAME?, #NUM!, to name a few. Let’s look at an example of error handling where we try to divide 1 by 0.

=IFERROR(1/0,-1000)

In the above example, we have divided 1 by 0. This calculation returns an error (#DIV/0!). The IFERROR function takes a calculation/formula as an input and returns the result of the calculation if it isnt’ an error. If the result is an error, the value we specified (-1000 in the above example) is returned.

If not handled, the formulae involving an erroneous calculation would also return an error. For example, if we add 10 numbers (using the SUM function) where #DIV/0! is one of the 10 numbers, then the SUM function would also return #DIV/0!.

3.?LEFT

This function returns first ’n’ characters of a string. Let’s look at an example to extract the first three characters of the string “Excel”. The output of the function will be “Exc”.

=LEFT("Excel",3)

4. RIGHT

This function returns the last ’n’ characters of a string. Let’s look at an example to extract the last three characters of the string “Excel”. The output of the function will be “cel”.

=RIGHT("Excel",3)

5. MID

This function returns a substring of a string if we input the starting number and number of characters to return. Let’s look at an example to extract the substring “xce” from the string “Excel”. In this example, the starting number is 2 and the number of characters to return is 3.

=MID("Excel",2,3)

6. FIND

This function returns the position of the first occurrence of a substring/character in a string. Let’s look at an example to find the position of the substring “ce” in the string “Excel”. The function returns 3, since, it is the position of the first and only occurrence of “ce”.

=FIND("ce","Excel")

The FIND function also enables a user to control the starting point of the search. Let’s look at an example to find the position of the second occurrence of “a” in “abca”. We’ll add a starting point argument, 2 (since we want to exclude the first ‘a’ from the search). The function returns 4.

=FIND("a","abca",2)

The search in FIND is case-sensitive, there is another function named SEARCH which is same as FIND but it’s search is not case-sensitive. FIND and SEARCH functions return a #VALUE! error if the specified substring/character is not found in a string.

7. ISNUMBER

This function returns TRUE if a given input is a number else returns FALSE. Let’s look at an example where we input “abc” and 123 to the function.

=ISNUMBER("abc") =ISNUMBER(123)

In the above example, the first formula returns FALSE as “abc” is not a number. The second formula returns TRUE as 123 is a number.

8. ISTEXT

This function returns TRUE if the given input is a string/text else returns FALSE. Let’s look at an example where we input “abc” and 123 to the function.

=ISTEXT("abc") =ISTEXT(123)

In the above example, the first formula returns TRUE as “abc” is a string/text. The second formula returns FALSE as 123 is a number.

9. SUBSTITUTE

This function replaces the ‘nth’ occurence of a substring in a string with a replacement string. This function is case-sensitive and ’n’ is 1 by default. Let’s look at an example to replace “Excel” with “MS Excel” in the string “Excel is cool”.

=SUBSTITUTE("Excel is cool","Excel","MS Excel")

Let’s look at another example to replace the second occurrence of “Excel” by “It” in the string “Excel is cool. Excel is developed by Microsoft”. The formula returns “Excel is cool. It is developed by Microsoft”.

=SUBSTITUTE("Excel is cool. Excel is developed by Microsoft","Excel","It",2)

10. TRIM

This function removes the leading and trailing whitespaces from a string.

=TRIM(" a ") returns "a"

11. COUNTA

This function returns the count of non-empty cells in a range of cells.

=COUNTA(A1:A10)

12. COUNTBLANK

This function returns the count of the empty cells in a range of cells. This function helps in finding the count of missing values in a column.

=COUNTBLANK(A1:A10)

13. COUNTIF

This function returns the count of a specified value in a range of cells. This is useful to find the count of missing values which are masked by values like 999, -1000, -, ?, etc. Let’s look at an example to count the number of occurrences of “-” in a range of cells. We can also specify a cell address containing the search criteria instead of “-”.

=COUNTIF(A1:A10,"-") =COUNTIF(A1:A10,B1)

?