How to Use Excel String Functions (Examples of 17 Functions)

Function 1 – TEXT Function

Syntax:

=TEXT(value, format_text)

Arguments Explanation:

Argument	Required/Optional	Explanation
value	Required	Value in a numeric form that has to be formatted.
format_text	Required	Specified number format.

Return Parameter:

A numeric value in a specified format.

We see the use of the TEXT function.

Function 2 – FIND Function

Syntax:

=FIND(find_text, within_text, [start_num])

Arguments Explanation:

Argument	Required/Optional	Explanation
find_text	Required	A text or a part of a text is to be searched for in a cell containing another text string.
within_text	Required	The cell containing the text where the defined character or part of the text will be searched for.
[start_num]	Optional	Defined position in the text string from where the character count will be initiated.

Return Parameter:

A numerical value in an integer form that defines the position of the defined character in a text string.

We see the FIND function in action.

Function 3 – CONCATENATE Function

Syntax:

The syntax of the CONCATENATE function is:

=CONCATENATE(text1,[text2],...)

Arguments:

The arguments can be in any format, e.g. text, number, cell reference, or an array of values.

Argument	Required/Optional	Value
text1	Required	The first value to be joined. It can be any text value, number, cell reference, or array of values.
[text2]	Optional	The second value is to be joined. It can be any text value, number, cell reference, or array of values.

Return Value:

Returns a joint text value combining all the arguments.

We see the CONCATENATE function in action.

Function 4 – MID Function

Syntax:

The syntax of the MID function is:

=MID(text,start_num,num_chars)

Argument:

Argument	Required/Optional	Explanation
text	Required	The string from which characters will be extracted. It can be any text value, number, or array.
start_num	Required	The starting position from which characters will be extracted. It can be a single number or an array of numbers.
num_chars	Required	The total number of characters that will be extracted. It can be a single number or an array of numbers.

Note:

The first argument text can be any text value, number, or array of text values or numbers. But whether it is a text value or a number, the return value will always be a text value.
The next two arguments start_num and num_chars can be any number or an array of numbers.
If you use an array argument, the formula will be an Array Formula, and you have to press Ctrl + Shift + Enter.

Return Value:

Returns a text value consisting of a specific number of characters starting from a specific position of a string.

Special Notes:

If the start_num argument is greater than the total number of characters of a string, the MID function will return an empty string.
Now, we will see the MID function in action.

Function 5 – SEARCH Function

The SEARCH function returns the number of characters after finding a specific character or text string, reading from left to right. This function Searches for a case-insensitive match. It works for both Array and Non-Array Formula and is available from Excel 2003.

Syntax:

The syntax of the SEARCH function is:

SEARCH(find_text,within_text,[start_num])

Arguments:

ARGUMENT	REQUIREMENT	EXPLANATION
find_text	Required	The text that is searched for. It can be a single text or an array of texts.
within_text	Required	The text value within which the find_text argument is searched for. It can be a single text value or an array of text values.
[start_num]	Optional	The position of the within_text argument from which it starts searching. It can be a single number or an array of numbers. The default is 1.

Note:

All three arguments can be either a single value or an array of values.
If at least one of the arguments is an array, the formula will become an Array Formula, and you must press Ctrl + Shift + Enter to enter it.
The third argument [start_num] is optional. The default is 1.

Return Value

Returns the number of characters at which the specific character or text string (find_text) is first found, reading from left to right.

We get the dataset showing how the SEARCH function works.

Function 6 – LEN Function

The LEN function in Excel returns the length of a given string. It is useful for a variety of purposes, including finding the number of characters in a given cell or range of cells or in a given string of text.

Syntax:

The LEN function is described with the following syntax:

=LEN(TEXT)

Arguments:

Argument	Required or Optional	Value
text	Required	The text for which to calculate length.

Note:

LEN reflects the length of text as a number.
This function works with numbers, but number formatting is not included.
LEN function returns zero in terms of empty cells.
We will see the LEN function in action.

Function 7 – RIGHT Function

Syntax:

=RIGHT(text,[num_chars])

Arguments Explanation:

Arguments	Required/Optional	Explanation
text	Required	Pass the text from which to extract characters on the right.
[num_chars]	Optional	Pass the number of characters to extract, starting on the right. The default value is 1.

Version:

The RIGHT function is available from Excel 2007 to the latest version.

Notes

If num_chars is not provided, it defaults to 1.
If num_chars is greater than the number of characters available, the RIGHT function returns the entire text string.
RIGHT will extract digits from numbers as well as text.
This function does not consider the formatting of any cell, like a date, currency, etc.
While writing this article, I’m using the Office 365.

We see every instance of using the RIGHT function.

Function 8 – SUBSTITUTE Function

The SUBSTITUTE function replaces existing text with new text in a text string.

Syntax:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Argument Explanation:

Argument	Required/Optional	Explanation
text	Required	The text to change
old_text	Required	The text to be replaced
new_text	Required	The text to replace old_text with
instance_num	Optional	The text to replace old_text with

We see the SUBSTITUTE function in action.

Function 9 – LEFT Function

Syntax:

=LEFT (text, [num_chars])

Arguments:

Argument	Required/Optional	Description
text	Required	The text string that contains the characters to extract.
num_chars	Optional	The number of characters to extract. By default, 1.

Return Parameter:

Returns a specified number of characters from the start of a text string.

We see the different instances of using the LEFT function.

Function 10 – CLEAN Function

Syntax:

=CLEAN(text)

Arguments:

Argument	Required/Optional	Explanation
text	Required	It is the text string from which you want to remove all of the non-printable characters

Return Value:

After entering the function, it will give you the text string free from all non-printable characters.

Note:

The CLEAN function can only remove the non-printable characters represented by numbers 0 to 31 in the 7-bit ASCII code.

Finally, we see the various instances of using the CLEAN function.

Function 11 – CHAR Function

Syntax:

=CHAR(number)

Argument Explanation:

Argument	Required/Optional	Explanation
number	Required	A number between 1 to 255 is assigned to a specific character

Return Parameter:

The CHAR function will return a character based on the number given as an argument.

Now, we see the output of the CHAR function in different instances.

Function 12 – PROPER Function

Usually, the PROPER function converts a text string into the proper case, the first letter in each word to uppercase, and all other letters to lowercase.

Syntax:

=PROPER(text)

Arguments:

Argument	Required/Optional	Explanation
text	Required	The text should be converted to a proper case. The text could, however, be a formula that yields text, text surrounded in quotation marks, or perhaps a reference to such a cell that includes the text.

Returning Parameter:

It returns the first letter of every word to uppercase and other letters to lowercase.

Now, we finally see the PROPER function in action.

Function 13 – EXACT Function

The EXACT function compares two texts and then returns TRUE (in case the texts are exactly the same) or FALSE (in case they are not exactly the same).

Syntax:

=EXACT (text1,text2)

Arguments Explanation:

Arguments	Required/Optional	Explanation
text1	Required	First text string
text1	Required	Second text string

Return Parameter:

TRUE or FALSE, depending on the exact match between the two arguments.

Now, let’s see the EXACT function in action.

Function 14 – TRIM Function

Syntax:

=TRIM (text)

Arguments Explanation:

Argument	Required/Optional	Explanation
text	Required	The text string from which to eradicate unnecessary spaces

Return Parameter:

Remove all spaces from a text string except for single spaces between words.

Let’s look at using the TRIM function in Excel.

Function 15 – CODE Function

Syntax:

=CODE(text)

Argument Explanation:

Argument	Required/Optional	Explanation
text	Required	For any text string, a code will be returned for the first character of the text string

Return Parameter:

A numeric number will be returned for the first character of the input text string. In general, the number returned by CODE represents the ASCII decimal code for a character. The CODE function was developed to operate in an ASCII/ANSI domain, and it only knows how to map characters to integers 0-255.

We look at using the CODE function in Excel.

Function 16 – REPT Function

Syntax:

=REPT(text, number_times)

Arguments Explanation:

Arguments	Required/Optional	Explanation
text	Required	Text or character that has to be repeated.
number_times	Required	The number of times repetition will occur.

Return Parameter:

Repeated number of the selected text or character in a cell.

Let’s see the use of the different instances of the REPT function in Excel.

Function 17 – VALUE Function

Syntax:

=VALUE(text)

Arguments:

text – The text value to convert into a number.

Return Parameter:

Converts a text string that represents a number to a number.

We see the use of the VALUE function in Excel.

Things to Remember

Data Truncation: When using functions like LEFT, RIGHT, or MID to extract a portion of a text string, there’s a risk of data truncation if the specified length exceeds the actual length of the text. This can result in missing or incomplete information.
Case Sensitivity Issues: Excel String Functions are generally case-sensitive. If there’s a mismatch in the text being compared or manipulated, the results may not be as expected, leading to errors or incorrect outputs.
Leading and Trailing Spaces: Extra leading or trailing spaces in text strings can cause discrepancies in search, comparison, and data manipulation operations. Functions like TRIM can be used to address this, but if overlooked, it can lead to issues

Download the Practice Workbook

Excel String Functions.xlsx

<< Go Back to Excel Function Categories | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Use Excel String Functions (Examples of 17 Functions)

Function 1 – TEXT Function

Function 2 – FIND Function

Function 3 – CONCATENATE Function

Function 4 – MID Function

Function 5 – SEARCH Function

Function 6 – LEN Function

Function 7 – RIGHT Function

Function 8 – SUBSTITUTE Function

Function 9 – LEFT Function

Function 10 – CLEAN Function

Function 11 – CHAR Function

Function 12 – PROPER Function

Function 13 – EXACT Function

Function 14 – TRIM Function

Function 15 – CODE Function

Function 16 – REPT Function

Function 17 – VALUE Function

Things to Remember

Leave a reply Cancel reply