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

  1. 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”.

 Use the find function to locate text in documents in Excel

  1. 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”.

 Use the Left function in Excel

  1. 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”.

Use the right function in Excel

  1. 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”.

Use the mid function in excel

  1. 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”.

Use the trim function in excel

  1. 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”.

Use the proper function in exel

  1. 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.

Use the substitute function in excel

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”.

  1. 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”

Use the Upper Function in excel

  1. 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”

Use the lower function in excel

  1. 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