What are text functions in excel. Working with strings in Excel. Excel text functions. Text concatenation operator

Often in Excel you have to process text strings in one way or another. It is very difficult to perform such operations manually when the number of lines is more than one hundred. For convenience, Excel has implemented a good set of functions for working with a string data set. In this article I will briefly describe the necessary functions for working with strings in the “Text” category and consider some of them with examples.

Functions of the "Text" category

So, let’s look at the main and useful functions of the “Text” category; you can familiarize yourself with the rest.

BATTEXT(Value) – a function that converts a number to a text type;
DLST(Value) is a helper function, very useful when working with strings. Returns the length of the string, i.e. number of characters contained in the line;
REPLACE(Old text, Starting position, number of characters, new text) – replaces the specified number of characters from a certain position in the old text with a new one;
SIGNIFICANT(Text) – converts text into a number;
LEVSIMV(String, Number of characters) – very useful feature, returns the specified number of characters, starting from the first character;
RIGHT(String, Number of characters) – analogue of the function LEVSIMV, with the only difference being that it returns characters from the last character of the string;
FIND(text to search, text in which we are searching, starting position) – the function returns the position from which the occurrence of the searched text begins. Characters are case sensitive. If you need to be case insensitive, use the function SEARCH. Only the position of the first occurrence in the string is returned!
SUBSTITUTE(text, old text, new text, position) – an interesting function, at first glance similar to the function REPLACE, but the function SUBSTITUTE is capable of replacing all occurrences in the string with a new substring if the “position” argument is omitted;
PSTR(Text, Starting position, Number of characters) – the function is similar to LEVSIMV, but is capable of returning characters from the specified position:
CONNECT(Text1, Text 2 .... Text 30) – the function allows you to connect up to 30 lines. You can also use the symbol " & ", it will look like this "="Text1" & "Text2" & "Text3"";

These are basically frequently used functions when working with strings. Now let's look at a couple of examples that will demonstrate how some of the functions work.

Example 1
Given a set of lines:

It is necessary to extract dates, invoice numbers from these lines, and also add a month field to filter the lines by month.

Let's extract the invoice numbers into column B. To do this, we will find the so-called key symbol or word. In our example, you can see that each invoice number is preceded by “No,” and the length of the invoice number is 6 characters. Let's use the FIND and PSTR functions. We write the following formula in cell B2:

= PSTR(A2; FIND("№";A2)+1;6)

Let's look at the formula. From line A2 from the position next after the found “No” sign, we extract 6 number characters.

Now let's extract the date. Everything is simple here. The date is located at the end of the line and takes up 8 characters. The formula for C2 is as follows:

= RIGHT(A2;8)

but the extracted date will be a string, to convert it to a date it is necessary to convert the text into a number after extraction:

= SIGNIFICANT(RIGHT(A2;8))

and then, set the display format in the cell, as described in the article ““.

And lastly, for the convenience of further filtering rows, we will introduce the month column, which we will get from the date. Only to create a month we need to remove the day and replace it with “01”. Formula for D2:

= SIGNIFICANT(CONNECT("01"; RIGHT(A2;6))) or = SIGNIFICANT("01"& RIGHT(A2;6))

Set the cell format " MMMM YYYY" Result:

Example 2
In the line " An example of working with strings in Excel" All spaces must be replaced with the sign "_", and before the word "Excel" add "MS".

The formula will be as follows:

=SUBSTITUTE(REPLACE(A1; SEARCH("excel";A1);0;"MS ");" ";"_")

In order to understand this formula, divide it into three columns. Start with SEARCH, the last one will be SUBSTITUTE.

All. If you have any questions, don't hesitate to ask

In program lines Microsoft Office Excel allows you to enter any information that users need to successfully complete their tasks. To be most productive, you need to be able to understand data in rows. This material will look at examples of text functions in Excel.

To find them in the program, go to the tab called "Formulas", open the list and select "Text".

"CONTACT"

This function allows the user to concatenate multiple lines together. The maximum number allowed for a connection is 255. Please note that you must enter spaces here yourself. The body of "CONCATENATE" must contain at least one argument. Let's look at an example of its recording and action:

=CONCATENATE("Argument1","","Argument2");
The result will be the following line: "Argument1 Argument2".

An alternative to this function for working with text is the & symbol, which is also responsible for concatenating strings.

"PSTR"

It is responsible for returning a piece of text in the specified number of characters from the required line. The line begins with the marked character. Key positions (text, starting position, number of characters). Each of them is defined as follows:

By text we mean a line or a link to a cell containing the necessary information.
The starting position marks the character number from which the string will be returned.
The number of characters is a natural integer indicating the number of characters to be returned, starting from the starting position.

The following situation can be given as an example. Cell A1 contains some text. We need to return the last two words; their total length of characters is 12. The character that needs to be returned in a specific phrase is numbered 12.

"SUBSTITUTE"

Replaces the finished text with the required one in the place specified by the user. Somewhat similar to the function called "Replace". But details about it will be presented later. The main difference of this option is that it automatically calculates occurrences of the required string and performs the replacement.

The specific function looks like this: text; old text; new text; occurrence number.

Let's look at each of the arguments:

Text refers to a link to specific cell or a string that contains this very information.
Old text is the original version of the data stored in the cell that needs to be replaced.
The new text is the string needed to perform the change to the old data version.
The occurrence number is an optional argument in a given text function in Excel. When specified, only matching occurrences will be replaced. In case of his absence, the change will occur everywhere.

"REPLACE"

Performs the function of replacing part of a string in a specific number of characters. The process starts from the character specified by the account to the new text. It looks like this: old text; starting position; number of characters; new text.

Let's look at each of the arguments of this text function in Excel separately:

A link to a specific cell or line containing previously entered information is called Old Text.
The starting position indicates the number of the character from which the replacement should be made (numbering goes from left to right).
The new text is a line that replaces the previously entered information, which was specified by the Start position and the Number of characters.

Now let's look at an example of using the text function in Excel. Cell A1 contains a line with the word "old". It starts from the 19th character and has a duration of six identical units. The replacement occurs with the word "new".

It is worth noting that this situation is quite twofold. On the one hand, if the user will work with a small number of cells, then this option will work great. But what to do in a situation where you have to perform a replacement among several thousand lines? In this case, it is worth turning to two other Excel text functions, which will be discussed a little later:

We will replace the initial position with “FIND”.
The number of characters will change to "LENGTH".

"DLST"

This text function in Excel helps the user to determine the length of a string. The result of its work will be the return of an integer indicating the number of characters in the text. It looks like this: =DLTR(text).

"FIND"

When working with text, it performs the function of returning a number that represents the occurrence of the first character found in a substring of the required text. If the data was not found, the error "#VALUE!" is returned instead of a number.

Here is its form in the document. But without specific arguments: =FIND(search text; search text; start position).

Now let's look at each of the above arguments individually:

The required string is the Search text.
The data among which the first argument is searched is the Search Text.
The starting position is not a required element. By default it has a value equal to one.

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

CONNECT

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

LOWER

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

CAPITAL

Text function CAPITAL makes all letters capital, i.e. converts them to uppercase. Same 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.

DLST

LEFT and RIGHT

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

PSTR

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

COINCIDENCE

Function COINCIDENCE 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 case does not play a big role for you (this happens in most cases), then you can apply a formula that simply checks the equality of two cells.

SPACE

Removes all extra spaces from the text, except single ones 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 irreplaceable. In the figure below you can see that the contents of cells A1 and B1 are exactly the same, but this is not the case. In cell A1 we intentionally put an extra space at the end of the word Excel. As a result, the function COINCIDENCE returned us the value FALSE.

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

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

REPEAT

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

FIND

Text function FIND finds the occurrence of one string within 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 letter “ 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 the function FIND, their main difference is that SEARCH not case sensitive.

SUBSTITUTE

Replaces specific text or character with the required value. Excel text function SUBSTITUTE used when it is known in advance what text needs to be replaced, and not its location.

The formula below 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:

REPLACE

Replaces characters located at a previously known location in a string with the required value. Excel text function REPLACE used when it is known where the text is located, but the text itself is not important.

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

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

That's it! We got acquainted with 15 text functions Microsoft Excel and looked at their effect on simple examples. I hope that this lesson came at just the right time for you, and you got at least a little from it useful information. All the best and good luck in learning Excel!

Functions are dependencies of some values on others, returning the results of performing certain actions on values that act as arguments. Functions in Excel make it possible to simplify formulas in worksheet cells in cases where they are long and/or complex.

An argument is a value that provides input information to a function.

List of text functions

For any of the listed functions you can get background information with a description, syntax and example, if you select the function of interest in the function wizard and click the “Help for this function” link.

Quick use of functions

"BATTEXT"

The help for the "BATTEXT" function says that in the control panel, in the "Language and Regional Standards" section, you can change the "bat" format to a different style, but, as a rule, no one succeeds in doing this. Due to this,analogue of this function in Russian written in built-in Office applications the VisualBasicforApplications programming language and is distributed as an add-on.

"LEVSIMV" and "PRAVSIMV"

The LEFT and RIGHT functions are used to trim unnecessary characters in cell values, both text and numeric. In cases where it is necessary for the trimmed values to be in the same cells in which the original text is located, it is faster and more convenient to trim the text bypassing the stage of entering formulas, using an add-on that allows remove extra characters.

"SIGNIFICANT"

A number in Excel can be formatted as text. No mathematical operations can be performed with such numbers; they are perceived by Excel as plain text. You can get a number formatted as text, for example, by placing an apostrophe in front of the numeric value, or by setting the cell format to text and then filling it with a numeric value. To convert such numbers into real numbers, the “VALUE” function is used. In addition, you can convert text to a number without using a formula, using an add-in that, in a given rangefinds numbers formatted as text and transforms them into real numbers.

“PECHSIMV”, “UPPERCASE”, “PROPNACH”, “SZHSPACE”, “LOWER”

Some text functions, such as “PREPCHYMBV”, “UPPER CAPITAL”, “PROPNACH”, “SPACES”, “LOWER” are often conveniently used not in the form of formulas entered into worksheet cells, but as a tool that allows you to get the final result of the transformation immediately in cells with source text. In such cases it is convenient to use

Excel offers its users as many as 3 functions for working with large and small letters in text (upper and lower case). These text functions make letters capital and small, or change just the first letter in a word to capitalize.

Formulas with Excel text functions

First, let's look at the example of 3 text messages Excel functions:

CAPITAL – this text function changes all letters in a word to capital letters.
LOWER – This function converts all text characters to lowercase, small letters.
PROPNACH - the function changes only the first letter in each word to a capital letter.

As you can see in the example in the figure, these functions do not require anything in their arguments other than the original text data, which should be converted in accordance with the user's requirements.

Despite such a wide selection of functions, Excel still needs a function that can replace the first letter with a capital one only for the first word in a sentence, and not in every word. However, to solve this problem, you can create your own custom formula using the same and other Excel text functions:

To solve this popular problem, you need to use additional Excel text functions in the formula: LEFT, RIGHT, and LENGTH.

The principle of operation of the formula for replacing the first letter in a sentence

If you look closely at the syntax of the above formula, you can easily see that it consists of two parts connected by the & operator.

The left side of the formula uses additional function LEVSIMV:

The purpose of this part of the formula is to change the first letter to a capital letter in the original text string of cell A1. Thanks to the LEFT function, you can get a certain number of characters starting from the left side of the text. The function requires 2 arguments to be filled in:

Text – a link to a cell with source text.
Number_characters – the number of characters returned from the left side (from the beginning) of the source text.

IN in this example only need to get the first 1 character from the original text string in cell A1. Next, the resulting character is converted to uppercase capital letter upper case.

The right side of the formula after the & operator is very similar in principle to the left side, only it solves a different problem. Its task is to convert all text characters into small letters. But this must be done so as not to change the first capital letter, for which the left side of the formula is responsible. Instead of the LEFT function on the right side of the formula, the RIGHT function is used:

The RIGHT text function works inversely with the LEFT function. It also requires dusting two arguments: the source text and the number of characters. But it returns a certain number of letters obtained from the right side of the source text. However, in in this case we cannot specify a fixed value as the second argument. After all, we do not know in advance the number of characters in the source text. Additionally, the length of different source text strings may vary. Therefore, we need to first calculate the length of the text line and subtract -1 from the resulting numerical value so as not to change the first capital letter in the line. After all, the first letter is processed by the left side of the formula and has already been transformed to suit the user’s requirements. Therefore, it should not be influenced by any function from the right side of the formula.

To automatically calculate the length of the source text, use the Excel text function - DLST (stands for line length). This function requires only one argument to be filled in - a link to the source text. As a result of the calculation, it returns a numeric value, so after the function =LENGTH(A1) we subtract -1. Which gives us the opportunity not to touch the first capital letter on the right side of the formula. As a result, the RIGHT function returns a text string without one first character to the LOWER function, which converts all characters of the text into small lowercase letters.

As a result of connecting both parts of the formula with the & operator, we get a beautiful text sentence, which, according to the rules, begins with the first capital letter. And all the other letters are small right up to the end of the sentence. Regardless of the length of the text, using the same formula we get the correct result.