Some Useful Functions in Excel
In this tutorial we’ll learn the use of different useful functions in Excel.
1. ‘&’ (ampersand) and CONCATENATE Function
‘&’ function adds the word/number/symbols to a cell. This function is very useful when you have to add a word to entire column or you have to combine two cells and defined as following:
=TextToCombine&TextToCombine
You can see the different type of examples for ‘&’ function. You can add any word/character in between the string.
CONCATENATE Function
Same can be achieved by CONCATENATE Function. It works same as ‘&’ Function. The function goes like this:
=CONCATENATE(Text1,Text2,...)
Find the examples in the below image. This function can be used for words/character/space or other string.
2. LEFT Function
LEFT function in excel display the specified number of character from a String or text. It is defined as following:
=LEFT(Original Text,Number Of Characters Required)
Examples are below
NOTE: LEFT function is also work for numbers also but it return it as string. So be careful while working with numbers.
3. RIGHT Function
RIGHT function is same as LEFT function but for Right side. RIGHT function is defined as:
=RIGHT(Original Text,Number Of Characters Required)
Examples are below
NOTE: RIGHT function is also work for numbers also but it return it as string. So be careful while working with numbers.
4. LOWER,UPPER and PROPER Functions
LOWER function converts all the characters in a text to Lower case
=LOWER(Text To Convert)
UPPER function converts all the characters in a text to Upper case.
=UPPER(Text To Convert)
Examples are below
PROPER Function converts a text string to proper case, it converts all the first letter of word to upper case and other letter to lower case.
5. T Function
T function returns if the entry is text and blank when it is not. T function defined as:
=T(Value)
6. LARGE Function
This function returns the number with the rank from the top and the function defined as:
=LARGE(List of numbers to Check,Rank to pick)
Largest number can be found by using MAX(Range to check) function too.
7. SMALL Function
This function is same as LARGE function and returns the number with the rank from the bottom and the function defined as:
=SMALL(List of numbers to Check,Rank to pick)
Smallest number can be found by using MIN(Range to check) function too.
8. TRIM Function
TRIM function removes all the extra spaces between the words except a single space in a string. This function is very helpful for data cleaning.
9. MATCH Function
MATCH function looks for an item in a list and shows its position. The function defined as:
=MATCH(item to find, Array to look for, type of match)
Type of match can be of three types:
0 - It will look for an exact match else return #N/A
1 - It will look for an exact match, or the next lowest number if no exact match exists else return #N/A
-1 - It will look for an exact match, or the next highest number if no exact match exists else return #N/A
10. REPT Function
REPT function repeat the text in the particular cell number of times we want.
=REPT("Text",numer of times)
=REPT("a",5) will return aaaaa
11. ISTEXT / ISNUMBER / ISERROR Function
ISTEXT function checks if the value is text or not and true or false. ISNUMBER function if the value is number or not and true or false.
ISERROR check if the value is #N/A,#VALUE etc. and return true or false accordingly.
=ISTEXT(Value).... ISTEXT("a") returns TRUE
=ISNUMBER(Value).. ISNUMBER(a) returns FALSE
=ISERROR(Value)... ISERROR(1/0) returns TRUE
12. CLEAN Function
CLEAN function is very useful to remove unrecognizable text in excel. This function is also very useful for data cleaning.
I’ll add more functions to this post and also write about the other functions too.