Essentials Excel

Excel Functions and Their Formulas / Syntax

Whatever you are planning, whether it is data analysis, construction of Dynamic Dashboard or Financial Model, use of excel functions is crucial. Microsoft excel provides rich categories of useful excel functions. You have the below excel functions available against the Formulas tab in the ribbon.

TEXT FUNCTIONS

CONCATENATE Formula EXACT Formula FIND Formula
LEFT Formula LEN Formula LOWER Formula
MID Formula PROPER Formula REPLACE Formula
REPT Formula RIGHT Formula SEARCH Formula
SUBSTITUTE Formula TEXT Formula TRIM Formula
UPPER Formula VALUE Formula


Concatenate Function:

You can use concatenate function to join two or more text values. The alternate to concatenate function is to use & ampersand sign to join the text.

Syntax : CONCATENATE(text1, text2, ….textn)

text1, text2, …textn

Text1,2,….n are 255 text values. In order to give space among text use double quotation marks as per above example.

LEFT, RIGHT and MID Functions:
LEFT function pick certain number of characters from the left side of the text. RIGHT function pick characters from the right side of text and MID function pick number of characters from in between.

LEFT Function Syntax : LEFT(text, number_of_characters)

RIGHT Function Syntax : RIGHT(text, number_of_characters)

MID Function Syntax : MID(text, start_character_number, number_of_characters)

start_character_number:

The first character number that you would like to pick.

number_of_characters:

Total number of characters you require.

REPT, UPPER, LOWER and PROPER Functions:
REPT function repeat the value number of times. In the above example REPT function is repeating l character 10 Times. UPPER function convert the characters to upper case. LOWER function change the characters to lower case and PROPER function convert the first character of every word to upper case.

TRIM and CLEAN Functions:
TRIM function removes all spaces from start, end of text and more than one spaces between words. CLEAN function remove non-printable characters from the text.



Leave a Reply

Your email address will not be published. Required fields are marked *