FUNCTIONS
Excel provides a huge number of built in functions and it used to perform a particular calculation or return information according to our spreadsheet data. These functions are categorized into text, logical, math, financial, statistical, etc.
In our website we provide a complete excel functions list which is categorized below. Each of the functions description with appropriate examples.
Built in Function
Categorise:-
3. Look Up and Reference Functions
8. Engineering Functions ( under progress )
9. Cube Functions ( under progress )
10. Statistical Functions ( under progress )
11. Database Functions ( under progress )
Text Functions:-
Categories of TEXT functions.
a) BAHTTEXT:-
In Microsoft Excel “BAHTTEXT” function
converts a number into THAI text,
with the suffix “Baht”.
Syntax of
the BAHTTEXT function:-
=BAHTTEXT(number)
Through the
syntax the number value convert into THAI
text.
Examples of BAHTTEXT function:-
The
following pictures show the example of the BAHTTEXT
function.
FORMULA:-
RESULT:-
b) CHAR:- In Microsoft Excel “CHAR” function returns a character
when we given a valid character code through the CHAR function.
CHAR function purpose to get character
from number.
Syntax of CHAR function:-
=CHAR(number) [A number between 1 and 255]
Example of CHAR function:-
The
following pictures show the example of the CHAR
function.
c) CLEAN:- In Microsoft Excel “CLEAN” function receives a text string
and returns the result of text has been cleaned of all non printable characters
and line break.
Syntax of CLEAN function:-
=CLEAN(text)
Example of CLEAN function:-
The
following pictures show the example of the CLEAN
function.
d) CODE:- In Microsoft Excel “CODE”
function converts the first character of a supplied text string or a single
character into the ASCII code.
Syntax of CODE function:-
=CODE(text)
Example of Code function:-
The
following pictures show the example of the CODE
function.
e)CONCATENATE :- In Microsoft Excel “CONCATENATE” function joins
a series of supplied text or other values in a combined text string.
Syntax of CONCATENATE function:-
=CONCATENATE(text)
[Press and
hold Ctrl key then click on each
cell you want to join. After that close the parenthesis and press ENTER key]
Example of Concatenate function:-
The
following pictures show the example of the CONCATENATE
function.
f) DOLLAR:- In Microsoft Excel “DOLLAR” function is used to convert a text string into decimal
placed currency format.
Syntax of DOLLAR function:-
=DOLLAR(number,decimal)
Example of Dollar function:-
The
following pictures show the example of the DOLLAR
function.
g) EXACT:- In Microsoft Excel “EXACT” function compares two text
string. If both value are the same then it returns “TRUE” otherwise it will returns “FALSE”.
Syntax of EXACT function:-
=EXACT(text 1,text 2)
Example of EXACT function:-
The
following pictures show the example of the EXACT
function.
h) FIND:- In Microsoft Excel “FIND” function is used to return the position of a specific
character or substring within a text string.
Syntax of FIND function:-
=FIND(find_text,within_text,[start_num])
In FIND function, the first two arguments
are required and the last one is optional.
i) find_text:- A specific character or substring
you want to find.
ii) within_text:-The text string to be search within.
Generally, you can input text string manually or through cell reference in the
formula.
iii) start_num:- It is an optional argument that
specifies from which character from which search cell begins. If we are not using this argument then the
search starts from the first character of the within_text string. If the FIND
function doesn’t find the find_text
character it’s returned a #VALUE! error.
Example of FIND function:-
The
following pictures show the example of the FIND
function.
i) FIXED:- In the Microsoft Excel “FIXED” function returns a text
represent of a number rounded to a
specified number of decimal places.
Syntax of FIXED function:-
=FIXED(number,[decimal_places],[no_commas])
1) number:- The number which the user want to
round.
2) decimal_places:- Enter the number of decimal places to
display in the result. It is an optional part. If you omitted this by default
the decimal places is assumed to be two.
3) no_commas:- In this parameter of we set it to “TRUE” the result will not displays
commas. If it sets to “FALSE” the
result will displays commas. It is also an optional part. If we omitted this the
result will display commas.
Example of FIXED function:-
The
following pictures show the example of the FIXED
function.
j) LEFT:- In the Microsoft Excel “LEFT” function returns a specified
number of characters from the left-most character of supplied text string.
Syntax of LEFT function:-
=LEFT(text,[num_of_chars])
1)text:- The string that you want to extract
from.
2)num_of_chars:- The number of characters that you
want to extract string from the left most character and it is an optional
argument. If you are not using this argument only one character is return. Example
of LEFT function:-
The
following pictures show the example of the LEFT
function.
k) LEN:- In Microsoft Excel “LEN” function returns the length of
the given text string. The length of a given text string as the number of
characters. (LEN function also count
the characters in numbers)
Syntax of LEN function:-
=LEN(text)
text:- The text string which calculate
length.
Example of LEN function:-
The
following pictures show the example of the LEN
function.
l) LOWER:- In Microsoft Excel “LOWER” function converts all letters
in the given string to lowercase.
Syntax of LOWER function:-
=LOWER(text)
text:- The text string to convert to
lowercase.
Example of LOWER function:-
The
following pictures show the example of the LOWER
function.
m) MID:- In Microsoft Excel “MID” function returns the specified
number of characters from the middle of the supplied text string.
Syntax of MID function:-
=MID(text,start_num,num_chars)
text:- The text string which extract from.
start_num:-
The first character location to extract.
num_chars:- The number of characters to extract.
Example of MID function:-
The
following pictures show the example of the MID
function.
n) PROPER:- In Microsoft Excel “PROPER” function whose first character
in each word will be converted to uppercase and remaining character to
lowercase.
Syntax of PROPER function:-
=PROPER(text)
text:- The text string which converts to
proper case.
Example of PROPER function:-
The
following pictures show the example of the
PROPER function.
o) REPLACE:- In Microsoft Excel “REPLACE” function replaces character
string with another group of character.
Syntax of REPLACE function:-
=REPLACE(old_text,start_num,num_chars,new_text)
i) old text:- Existing string value.
ii) start_num:- The location of old text to start
replacing character.
iii) number_chars:- The number of character replace in
old text.
iv) new_text:- Group of replacement character.
Example of REPLACE function:-
The
following pictures show the example of the
REPLACE function.
p) REPT:- In Microsoft Excel “REPT” function is used to repeat the characters which given a number of times.
Syntax of REPT function:-
=REPT(text,number_times)
i) text:- The text to repeat.
ii) number_times:- The number times used to repeat the
text.
Example of REPT function:-
The
following pictures show the example of the
REPT function.
q)RIGHT:- In Microsoft Excel “RIGHT” function returns a specified number of characters from the right-most character of supplied text string.
Syntax of RIGHT function:-
=RIGHT(text,[number_of_chars])
1)text:- The string that you want to extract from.
2)number_of_chars:- The number of characters that you want to extract string from the right most character and it is an optional argument. If you are not using this argument only one character is return.
Example of RIGHT function:-
The following pictures show the example of the RIGHT function.
r) SEARCH:- In Microsoft Excel “SEARCH” function returns the result of the substring location in a given string and the search function is not case sensitive.
Syntax of SEARCH function:-
=SEARCH(find_text,within_text,start_text)
i) find_text:- The text that you want to find.
ii) within_text:- The string to search within.
iii) start_num:- It is the location of position in
string where the searching part will start and it is an optional part.
Example of SEARCH function:-
The
following pictures show the example of the
SEARCH function.
s) SUBSTITUTE:- In Microsoft Excel “SUBSTITUTE” function replaces text in a given string by
matching and it is a case sensitive without supporting wild cards characters.
Syntax of SUBSTITUTE function:-
= SUBSTITUTE(text,old_text,new_text,[instance_num])
i) text:- The text which we change within.
ii) old_text:- The text to replace.
iii) new_text:- The text to replace with.
iv) instance_num:- The instance to replace and it is an optional part if omitted all instance are replaced.
Example of SUBSTITUTE function:-
The
following pictures show the example of the
SUBSTITUTE function.
t) T:- In Microsoft Excel “T” function checks whether a value is
text and returns text if it is or returns the double quotes or empty text if it
is not.
Syntax of T function:-
=T(value)
value:- The value to return as text.
Example of T function:-
The following
pictures show the example of the T
function.
u) TEXT:- In
Microsoft Excel “TEXT” function
converts a value to text in a specific number format.
Syntax of TEXT function:-
=TEXT(value,format_text)
i) value:- The value to convert to text:
ii) format_text:- The format used to display the
result.
Example of TEXT function:-
The
following pictures show the example of the
TEXT function.
v) TRIM:- In Microsoft Excel “TRIM” function removes all spaces from
a text string except single space between two words.
Syntax of TRIM function:-
=TRIM(text)
text:- The text you want to remove extra
spaces from.
Example of TRIM function:-
The
following pictures show the example of the
TRIM function.
w) UPPER:- In Microsoft Excel “UPPER” function converts a text string
to all uppercase letters.
Syntax of UPPER function:-
=UPPER(text)
text:- The text you want to uppercase.
Example of UPPER function:-
The
following pictures show the example of the
UPPER function.
x) VALUE:- In Microsoft Excel “VALUE” function converts text string
into numeric value.
Syntax of VALUE function:-
=VALUE(text)
text:- The text you want to convert to number.
Example of VALUE function:-
The
following pictures show the example of the
VALUE function.
0 Comments:
Post a Comment