Contents
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.
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.
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.
PROPNACH
Text function PROPNACH capitalizes the first letter of each word, and converts all others to lowercase.
Every first letter that follows a non-letter character is also converted to uppercase.
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.
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.
PSTR
Text function PSTR returns the specified number of characters, starting at the specified position. A space counts as a character.
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.
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.
TRIM
Removes all extra spaces from the text, except for single spaces between words.
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.
By applying the function TRIM to the value of cell A1, we will remove all extra spaces from it and get the correct result:
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.
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.
This function is case sensitive…
… 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.
SEARCH
Text function SEARCH very similar to function TO FIND, their main difference is that SEARCH case insensitive.
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”:
Replaces only the first occurrence of the word “Excel”:
Removes all spaces from a text string:
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”.
Replaces the first five characters of a text string, i.e. the word “Excel” to “Word”.
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!