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.
|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|
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)
The first character number that you would like to pick.
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.