1. Text functions


Text functions can perform various treatments on strings of characters. For example :

  • Change the case of a string, i.e. convert it to upper or lower case
  • Extract a part of a string of characters
  • Search for a string within a string
  • Replace text in a string with another text
  • etc...

Fonction LEN

The LEN function returns the number of characters in a string.

Syntax

LEN(text)

Arguments

text: the string we want to have its number of characters

Result

The number of characters of the string given as argument

Example

The formula written in B2 is : "=LEN(A2)"

Excel 2016 LEN function

UPPER, LOWER and PROPER functions

The UPPER, LOWER and PROPER functions change the case of a string of characters:

  • The UPPER function converts a string of characters to upper case
  • The LOWER function converts a string of characters to lower case
  • The PROPER function puts the first letter of each word of a string in upper case and the rest of the word in lower case

Syntax

UPPER(text)

LOWER(text)

PROPER(text)

Arguments

text : the character string to be converted

Résult

The string after conversion

Example

The formula written in B2 is : "=UPPER(A2)"
The formula written in C2 is : "=LOWER(A2)"
The formula written in D2 is : "=PROPER(A2)"

Excel 2016 UPPER, LOWER and PROPER functions

MID Function

The MID function returns a part of a string.

Syntax

MID(text;start_num;num_char)

Arguments

text : the string from which a part will be extracted
start_num : number of the starting character of the extraction
num_char : number of characters to be extracted from the start_num position

Result

The string extracted from the one given as an argument having num_char characters from the start_num poisition

Example

The formula written in B2 is : "=MID(A2,5,10)"

Excel 2016 MID function

Note that when there are not enough characters left than specified in the third argument, then Excel returns the remaining characters and does not return an error. This is the case for example in cell B2 where 8 characters are returned instead of 10.

FIND and SEARCH functions

The functions FIND and SEARCH search for a character or string of characters in another string and return the position of the first occurrence found. These functions return the error #VALUE! if they do not find.

The difference between these two functions is that FIND is case sensitive, but SEARCH is not. This means that for SEARCH "e" and "E", for example, it is the same thing. Another difference is that with the SEARCH function, it is possible to use the wildcards "?" and "*" in the search text.

 

Syntax

TROUVE(find_text;within_text;start_num)
SEARCH(find_text;within_text;start_num)

Arguments

find_text : the character or string you wish to search for
within_text : the string where find_text will be searched
start_num : position in within_text where the search will start. This means that characters before this position will be ignored in the search. This argument is optional. If it is ignored, the search will start at position 1.

Result

The position where find_text is found in within_text. Or the error #VALUE! if the searched text is not found.

Example

The formula written in B2 is : "=FIND("e",A2,1)"
The formula written in C2 is : "=SEARCH("e",A2,1)"

Excel 2016 FIND and SEARCH functions

SUBSTITUTE function

The SUBSTITUTE function replaces characters with other characters in a text.

Syntax

SUBSTITUTE(text; old_text; new_text; instance_num)

Arguments

text : the string where the replacement will take place
od_text : the text that will be searched to be replaced
new_text : the text that will replace old_text in the first argument text
instance_num : number of the occurrence of old_text that will be replaced. If it is ignored, then all occurrences of old_text will be replaced by new_text.

Result

The text obtained after the replacement

Example

The formula written in B2 is : "=SUBSTITUTE(A3,"Small","Large",1)" : the first occurrence is replaced
The formula written in C2 is : "=SUBSTITUTE(A3,"Small","Large",2)" : the second occurrence is replaced
The formula written in D2 is : "=SUBSTITUTE(A3,"Small","Large",3)" : nothing is replaced because there is no third occurrence
The formula written in E2 is : "=SUBSTITUTE(A3,"Small","Large")" : all occurrences are replaced, as the fourth argument is ignored

NB. Note that the SUBSTITUTE function is case sensitive; "small" is not considered an occurrence of "Small".

Excel 2016 SUBSTITUTE functions

Exercises

Exercise - Text Functions


<< Excel 2016 functions

2. Date functions >>