Top 10 Text Functions in Excel
Top 10 TEXTS Functions in Excel
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 using native functions. Check out the
Excel Text Function Training Video
- FIND:
Definition: The FIND function is used in Excel to locate the position of the required text string within the another available text string
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”.
- TRIM:
Definition: TRIM function in Excel remove all the extra spaces from the text except for the single spaces between the words. It is used when the text has irregular spacing between words.
Arguments/Syntax: TRIM(text)
text: It is required argument. It is the text string that contains the irregular spacing between the words
Example: Suppose, in Excel the cell A2 contains a full name of a person as “Roland Watson”. But in between the first name (“Roland”) and last name (“Watson”) there are two blank spaces. Now, if user wants to remove the extra blank spaces then TRIM function can be used; for example, TRIM(”Roland Watson”). The output of this function is “Roland Watson”.
- 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”.
- SUBSTITUTE:
Definition: SUBSTITUTE function in Excel is used to replace the any part of the old text string with the new text string.
Arguments/Syntax: SUBSTITUTE (text,old_text,new_text,[instance_num])
text: It is required argument. The text containing text for which user wants to substitute characters.
old_text: It is required argument. It is the text user wants to replace
new_text: It is required argument. It is the text user wants to replace the old text with
[instance_num]: It is optional argument. Specifies which occurrence of old_text user wants to replace with new_text. If user specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.Example: Suppose, in Excel the cell A2 contains a full name of a person as “Roland Watson”. Now, if user wants to substitute “Roland” to “John” then SUBSTITUE function can be used; for example, SUBSTITUTE(“Roland Watson”,”Roland”,”John”). The output of this function is “John 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”
- REPT:
Definition: REPT function in Excel repeats the mentioned text by given number of times. REPT function can be used to fill a cell with a number of instances of a text string
Arguments/Syntax: REPT(text, number_times)
text: It is required argument. It takes the text string that user wants to repeat mutilple times
number_times: It is required argument. It takes a positive number specifying the number of times to repeat text
Example: REPT(“-“,5) = “—–“ Displays a dash (-) 5 times