Data Used in Formula

Hello

Formula Result Commentary

=LEFT("Hello",2) He Extract 2 characters from the left of the text Hello

Extract 2 characters from the left of the text Hello

=LEFT(A2,2) He
(which is in the cell A2)

If the number of characters are not specified, the first

=LEFT(A2) H
character from the left is extracted

If the number of characters specified is greater than

=LEFT(A2,8) Hello
the length of the text, entire text is returned

If the number of characters specified is 0, it returns a

=LEFT(A2,0)
blank ("")

If the number of characters specified is negative, it

=LEFT(A2,-1) Err:502
returns an error
 Data Used in Formula
Hello

Formula Result Commentary

=RIGHT("Hello",2) lo Extract 2 characters from the right of the text Hello
Extract 2 characters from the right of the text Hello
=RIGHT(A2,2) lo
(which is in the cell A2)
If the number of characters are not specified, the first
=RIGHT(A2) o
character from the right is extracted
If the number of characters specified is greater than
=RIGHT(A2,8) Hello
the length of the text, entire text is returned
If the number of characters specified is 0, it returns a
=RIGHT(A2,0)
blank ("")
If the number of characters specified is negative, it
=RIGHT(A2,-1) Err:502
returns an error
 Data Used in Formula
Trump Excel

Formula Result Commentary

=MID("Hello",1,2) He Extracts 2 characters beginning from the 1st character

Extracts 5 characters beginning from the 7th

=MID(A2,7,5) Excel
character
If the start number is larger than the length of the
=MID(A2,15,5)
string, formula returns a blank ("")
If the number of characters specified are larger than
=MID(A2,1,15) rump Excel the total number of characters, the entire string is
returned (beginning from the starting number)
=MID(A2,-1,15) Err:502 Returns an error if start number is negative (or 0)
Returns an error if number of characters specified is
=MID(A2,1,-1) Err:502
negative.
 Data Used in Formula
Hello 123

Formula Result Commentary

=LEN("Hello") 5 Returns the number of characters in the text Hello
Returns the number of characters in the text Hello
=LEN(A2) 5
(which is in cell A2)

=LEN("123") 3 Returns the number of characters for numbers as well

=LEN(B2) 3 Returns the number of characters for numbers as well

 Data Used in Formula
HELLO HelLo

Formula Result Commentary

=LOWER("HELLO") hello All upper case alphabets are converted to lower case

All upper case alphabets (in cell A2) are converted to

=Lower(A2) hello
lower case
All upper case alphabets (in cell B2) are converted to
=Lower(B2) hello
lower case
=LOWER("123@!") 123@! Numbers and special characters remain unaffected
 Data Used in Formula
trump eXcel
[Link]
It's Awesome

Formula Result Commentary

Capitalizes the first alphabet of the word, and rest all
=PROPER("hello") Hello
are converted to lower case
Capitalizes the first alphabet of each word, and rest
=PROPER(A2) Trump Excel
all are converted to lower case
This could be one disadvantage, as it capitalizes any
=PROPER(A4) It'S Awesome
alphabet that follows a special characters
 Data Used in Formula
hello HelLo

Formula Result Commentary

=UPPER("hello") HELLO All lower case alphabets are converted to upper case

All lower case alphabets (in cell A2) are converted to

=UPPER(A2) HELLO
upper case
All lower case alphabets (in cell B2) are converted to
=UPPER(B2) HELLO
upper case
=UPPER("123@!") 123@! Numbers and special characters remain unaffected
 Data Used in Formula
Excel E
Excellent Excel

Formula Result Commentary

Finds the first occurrence of "E" in Excel. Since we did
=FIND("E",A2) 1 not give the start number, it used the default value of
1

Finds the first occurrence of "E" in Excel. Specified the

=FIND("E",A2,1) 1
start number as 1 in this case.

Finds the first occurrence of "e". Note that FIND is

=FIND("e",A2) 4
case sensitive, so it returns 4 (and not 1)

Finds the first occurrence of the value in cell B2

=FIND(B2,A2) 1
(which is "E") in Excel.

Finds the first occurrence of the value in cell B2

(which is "E"), but starts with the second character. It
=FIND(B2,A3,2) 11 does not mean that this does not count the first E. It
counts the first E as well, but ignores it and start
finding from 2nd character onwards
 Data Used in Formula
abcdefghij
trumpexcel@[Link]

Formula Result Commentary

Starts from the 5th character ("e") and replace 4
=REPLACE(A2,5,4,"*") abcd*ij
characters (efgh)with an asterisk (*)
=REPLACE(A3,11,10,"") trumpexcel Removes the "@[Link]" with a blank
=REPLACE(A2,-1,4,"*") Err:502 Start Number can not be negative or zero
Number of characters can not be negative. It can be
=REPLACE(A2,1,-1,"*") *abcdefghij
zero
 Data Used in Formula
abcdabcdabcd
123@@@123

Formula Result Commentary

=SUBSTITUTE(A2,"a","Z",1) Zbcdabcdabcd Replaced the first "a" by "Z".
=SUBSTITUTE(A2,"a","Z",3) abcdabcdZbcd Replaced the third "a" by "Z".
If Instance number is not mentioned, all the instances
=SUBSTITUTE(A2,"a","Z") ZbcdZbcdZbcd
get replaced
Substitute is case-sensitive. Since it did not find "A", it
=SUBSTITUTE(A2,"A","Z",1) abcdabcdabcd
changed nothing
=SUBSTITUTE(A2,"A","Z",-1) Err:502 Instance Number can not be negative or 0
=SUBSTITUTE(A3,"@","|") 123|||123 Works for numbers and special characters as well
 Data Used in Formula
Tom 20,000 Feb-14

Formula Result Commentary

=TEXT(B2,"0.00") 20000.00 Changes the format to add 2 decimal points
=A2&" did sales of Tom did sales of Created text with the desired format for the sales
"&TEXT(B2,"0.00") 20000.00 number
=A2&" did sales of
Tom did sales of Created text with the desired format for the sales
"&TEXT(B2,"0.00")&" on
20000.00 in February number and month
"TEXT(C2,"mmm")