Course sections

Introduction to Microsoft Excel, Lecture 5

System defined Text Functions

Text Functions

Transforming and cleaning text is an essential for any analyst, teacher or secretary.  Luckily Excel provides a quick and easy ways to transform text in a spreadsheet using native functions.

  • FIND:

Definition: The FIND function is used in Excel to locate the position of the required text string within another available text strin
Arguments/Syntax: FIND (find_text, within_text, [start_num])
find_text: It is required argument. It takes the text user wants to find
within_text: It is required argument. The text containing the text user wants to find
start_num: It is optional argument. It specifies the character at which to start the search. By default, it takes the value 1.
Example: Suppose, in Excel the cell A2 contains a full name of a person as “Roland Watson”. Now, if user wants to find the position of text “an” within the name of the person, then FIND functions can be used; for example, FIND (“an”,” Roland Watson” ,1). The output of this function is 4 as the starting position text “an” is 4 within” Roland Watson”. 

  • LEFT:

Definition: LEFT function in Excel returns the left-hand side characters from a text string based on the number of characters specified by the user.
Arguments/Syntax: LEFT (text, [num_chars])
text: It is required argument. It is the text string that contains the characters user wants to extract
num_chars: It is optional argument. It specifies the number of characters user wants to extract from left side. By default, it takes the value 1.
Example: Suppose, in Excel the cell A2 contains a full name of a person as “Roland Watson”. Now, if user wants to extract the first 5 letters of the name then LEFT functions can be used; for example, LEFT (” Roland Watson” ,5). The output of this function is “Rolan”. 

  • RIGHT:

Definition: RIGHT function in excel returns the right-hand side characters from a text string based on the number of characters specified by the user.
Arguments/Syntax: RIGHT (text, [num_chars])
text: It is required argument. It is the text string that contains the characters user wants to extract
num_chars: It is optional argument. It specifies the number of characters user wants to extract from right side. By default, it takes the value 1.
Example: Suppose, in Excel the cell A2 contains a full name of a person as “Roland Watson”. Now, if user wants to extract the last 5 letters of the name then RIGHT function can be used; for example, RIGHT (” Roland Watson” ,5). The output of this function is “atson”.

  • MID:

Definition: MID function in Excel returns the number of characters from a text string based on the starting position and number of characters specified by the user.
Arguments/Syntax: MID (text, start_num, num_chars)
text: It is required argument. It is the text string that contains the characters user wants to extract
start_num: It is required argument. It specifies the character at which to start the extract.
num_chars: It is required argument. It specifies the number of characters user wants to extract from the start position
Example: Suppose, in Excel the cell A2 contains a full name of a person as “Roland Watson”. Now, if user wants to extract the “and” text from the name then MID function can be used; for example, MID (” Roland Watson”,4,3). The output of this function is “and”.

  • PROPER:

Definition: PROPER function in Excel capitalizes the first letter of each of the words in the string and converts all the other letters of the words to lower case.
Arguments/Syntax: PROPER (text)
text: It is required argument. It takes the text string that user wants to partially capitalize.
Example: Suppose, in Excel the cell A2 contains a full name of a person as “RolAnD WatsON”. Now, if user wants to make the name in proper way then PROPER function can be used; for example, PROPER (” RolAnD WatsON”). The output of this function is “Roland Watson”.

  • UPPER:

Definition: UPPER function in excel capitalizes all the letter of each of the words in the string Arguments/Syntax: UPPER (text)
text: It is required argument. It takes the text string that user wants to capitalize completely.
Example: Suppose, in excel the cell A2 contains a full name of a person as “RolAnD WatsON”. Now, if user wants to convert the name in full capital letters then UPPER function can be used; for example, UPPER (” RolAnD WatsON”). The output of this function is “ROLAND WATSON”

  • LOWER:

Definition: LOWER function in Excel converts all the letter of each of the words in the string in small letters
Arguments/Syntax: LOWER (text)
text: It is required argument. It takes the text string that user wants to convert into small letters completely.
Example: Suppose, in Excel the cell A2 contains a full name of a person as “RolAnD WatsON”. Now, if user wants to convert the name in small letters completely then LOWER function can be used; for example, LOWER (” RolAnD WatsON”). The output of this function is “roland watson”

 

WhatsApp chat