Top 12 Excel 2010 formulas

LEN - It will count the number of text in a given string. The string can be referenced from another column or hard coded inside the string.

=LEN(A1)

=LEN("1234asdf") Output:8

LOWER - This formula makes all character to lowercase

=LOWER(A1)

=LOWER(AsDF) - Output:asdf

UPPER - This formula makes all character to upper case

=UPPER(A1)

=LOWER(AsDF) - Output:ASDF

PROPER - This is like Init Caps or capitalizes the first character.

=PROPER(A1)

=PROPER(asdf) Output:Asdf

TRIM - Used to remove trailing and preceding spaces. Remove all blank space before and after the content.

=TRIM(A1)

=TRIM("  asdf 1234  ") Output: asdf 1234

Note: It won’t remove space in between the words

SUBSTITUTE - This formula used to replace or substitute a text with another text.

=SUBSTITUTE(B1,” “,”-“)

=SUBSTITUTE("asdf 1234",” “,”-“) Output:asdf-1234

=SUBSTITUTE("asdf 1234",” “,”!@#$“) Output:asdf!@#$1234

CONCATENATE - This formula is used to concatenate or join two or more text

=CONCATENATE("1234","asdf") Output: 1234asdf

TEXT - Used to format an existing column data to a new format. This is very useful to change date format.

=TEXT("12/31/2001","DD-MMM-YYYY") Output: 31-Dec-2001

LEFT - Return the number of characters from left

=LEFT("asdf",2) Output: as

RIGHT- Return the number of characters from right

=LEFT("asdf",2) Output: df

HYPERLINK - Used to open a folder or file from the local or network share folder.

=HYPERLINK("C:\Blog","Open Blog Folder") Output: when the user clicks the link it will open the folder C:\Blog from the local machine.

Count: This will count the number of NUMBERs in the given range. It will ignore the string or any other special data.  

=COUNT(H6:H11) Output: Identify the total number of excel cells which has numbers and ignore all other cells. It will provide the number of cells matching the count.