Excel text functions in examples

Excel offers a large number of functions with which you can process text. The scope of text functions is not limited to text only, they can also be used with cells containing numbers. As part of this lesson, we will look at examples of the 15 most common Excel functions from the category Text.

STsEPIT

To concatenate the contents of cells in Excel, along with the concatenation operator, you can use the text function STsEPIT. It sequentially concatenates the values ​​of the specified cells in one row.

Excel text functions in examples

LOWER

If Excel needs to make all letters lowercase, i.e. convert them to lowercase, a text function will come to the rescue LOWER. It does not replace characters that are not letters.

Excel text functions in examples

REGULATORY

Text function REGULATORY makes all letters capitalized, i.e. converts them to upper case. As well as LOWER, does not replace characters that are not letters.

Excel text functions in examples

PROPNACH

Text function PROPNACH capitalizes the first letter of each word, and converts all others to lowercase.

Excel text functions in examples

Every first letter that follows a non-letter character is also converted to uppercase.

Excel text functions in examples

DLSTR

In Excel, you can count the number of characters contained in a text string by using the function DLSTR. Spaces are taken into account.

Excel text functions in examples

LEFT and RIGHT

Text functions LEVSIMV и RIGHT return the given number of characters, starting from the beginning or from the end of the string. A space counts as a character.

Excel text functions in examples

Excel text functions in examples

PSTR

Text function PSTR returns the specified number of characters, starting at the specified position. A space counts as a character.

Excel text functions in examples

EXACT

Function EXACT allows you to compare two text strings in Excel. If they match exactly, then TRUE is returned; otherwise, FALSE. This text function is case sensitive but ignores formatting differences.

Excel text functions in examples

If the case does not play a big role for you (it happens in most cases), then you can apply a formula that simply checks the equality of two cells.

Excel text functions in examples

TRIM

Removes all extra spaces from the text, except for single spaces between words.

Excel text functions in examples

In cases where the presence of an extra space at the end or beginning of a line is difficult to track, this function becomes simply indispensable. The figure below shows that the contents of cells A1 and B1 are exactly the same, but this is not so. In cell A1, we deliberately put an extra space at the end of the word Excel. As a result, the function EXACT returned us the value FALSE.

Excel text functions in examples

By applying the function TRIM to the value of cell A1, we will remove all extra spaces from it and get the correct result:

Excel text functions in examples

Function TRIM useful to apply to data that is imported into Excel worksheets from external sources. Such data very often contains extra spaces and various non-printable characters. To remove all non-printable characters from the text, you must use the function PRINT.

REPEAT

Function REPEAT repeats a text string the specified number of times. The string is given as the first argument of the function, and the number of repetitions as the second.

Excel text functions in examples

TO FIND

Text function TO FIND finds the occurrence of one string in another and returns the position of the first character of the search phrase relative to the beginning of the text.

Excel text functions in examples

This function is case sensitive…

Excel text functions in examples

… and can start viewing the text from the specified position. In the figure below, the formula starts scanning from the fourth character, i.e. c letters “r“. But even in this case, the position of the character is considered relative to the beginning of the text being viewed.

Excel text functions in examples

SEARCH

Text function SEARCH very similar to function TO FIND, their main difference is that SEARCH case insensitive.

Excel text functions in examples

Excel text functions in examples

SUBSTITUTE

Replaces the specified text or character with the desired value. In excel text function SUBSTITUTE are used when it is known in advance which text needs to be replaced, and not its location.

The following formula replaces all occurrences of the word “Excel” with “Word”:

Excel text functions in examples

Replaces only the first occurrence of the word “Excel”:

Excel text functions in examples

Removes all spaces from a text string:

Excel text functions in examples

ZAMENIT

Replaces characters located at a known location in a string with the desired value. In excel text function ZAMENIT are used when it is known where the text is located, while it itself is not important.

The formula in the example below replaces 4 characters starting from the seventh position with the value “2013”. For our example, the formula would replace “2010” with “2013”.

Excel text functions in examples

Replaces the first five characters of a text string, i.e. the word “Excel” to “Word”.

Excel text functions in examples

That’s all! We got acquainted with 15 text functions of Microsoft Excel and looked at their action on simple examples. I hope that this lesson came in handy for you, and you got at least a little useful information from it. Good luck and success in learning Excel!

Leave a Reply