Text functions and formulas in excel. Working with strings in Excel

• less than or equal sign<=»;

• "not equal" sign<>».

Text concatenation operator

The special character "&" (ampersand) is used for this purpose. With it, you can connect different fragments into one whole - the same principle as with the "CONNECT" function. Here are some examples:

1. If you want to combine text in cells, then you need to use the following code.

1. In order to insert some character or letter between them, you need to use the following construction.

1. You can combine not only cells, but also ordinary characters.

Any text other than links must be enclosed in quotation marks. Otherwise, the formula will throw an error.

Please note that quotes are used exactly as in the screenshot.

You can use the following operators to define links:

• in order to create a simple link to the desired range of cells, it is enough to specify the first and last cell of this area, and the “:” symbol between them;
• to combine links, use the sign ";";
• if it is necessary to determine the cells that are at the intersection of several ranges, then a “space” is placed between the links. In this case, the value of the cell "C7" will be displayed.

Since only it falls under the definition of "intersection of sets." This is the name of this operator (space).

Use of links

While working in the Excel editor, you can use links of various kinds. However, most novice users know how to use only the simplest of them. We will teach you how to correctly enter links of all formats.

Simple Links A1

As a rule, this type is used most often, since it is much more convenient to compose them than the rest.

• columns - from A to XFD (no more than 16384);
• lines - from 1 to 1048576.

Here are some examples:

• cell at the intersection of row 5 and column B - "B5";
• range of cells in column B starting from row 5 to row 25 - "B5:B25";
• the range of cells in row 5 starting from column B to F - "B5:F5";
• all cells in row 10 - "10:10";
• all cells in rows 10 to 15 - "10:15";
• all cells in column B - "B:B";
• all cells in columns from B to K - "B:K";
• the range of cells from B2 to F5 is "B2-F5".

Sometimes formulas use information from other sheets. It works as follows.

The second sheet contains the following data.

If there is a space in the sheet name, then in the formula it must be indicated in single quotes (apostrophes).

Absolute and relative links

The Excel editor works with three types of links:

• absolute;
• relative;
• mixed.

Let's consider them more carefully.

All of the previously mentioned examples belong to relative cell addresses. This type is the most popular. The main practical advantage is that the editor will change the references to a different value during the migration. In accordance with where exactly you copied this formula. For the calculation, the number of cells between the old and new position will be taken into account.

Imagine that you need to stretch this formula to the entire column or row. You will not manually change letters and numbers in cell addresses. It works as follows.

1. Enter the formula for calculating the sum of the first column.

1. Press the hotkeys Ctrl +C . In order to transfer the formula to the next cell, you need to go there and press Ctrl + V .

If the table is very large, it is better to click on the lower right corner and, without releasing your finger, stretch the pointer to the end. If there is little data, then copying using hotkeys is much faster.

1. Now look at the new formulas. The change in the column index happened automatically.

If you want all links to be preserved when transferring formulas (that is, so that they do not change automatically), you need to use absolute addresses. They are specified as "$B$2".

As a result, we see that no changes have occurred. All columns display the same number.

This type of address is used when it is necessary to fix only a column or row, and not all at the same time. You can use the following constructs:

• $D1, $F5, $G3 - for fixing columns;
• D$1, F$5, G$3 - to fix rows.

Work with such formulas only when necessary. For example, if you need to work with one constant row of data, but only change the columns. And most importantly - if you are going to calculate the result in different cells that are not located along the same line.

The fact is that when you copy the formula to another line, the numbers in the links will automatically change by the number of cells from the original value. If you use mixed addresses, then everything will remain in place. This is done in the following way.

1. Let's use the following expression as an example.

1. Let's move this formula to another cell. Preferably not on the next and on another line. Now you can see that the new expression contains the same string (4) but a different letter, since it was the only one that was relative.

3D Links

The concept of "three-dimensional" includes those addresses in which a range of sheets is indicated. An example formula looks like this.

In this case, the result will correspond to the sum of all cells "A5" on all sheets, starting from 1 to 4. When compiling such expressions, the following conditions must be observed:

• arrays cannot use such references;
• three-dimensional expressions are forbidden to be used where there is an intersection of cells (for example, the "space" operator);
• When creating formulas with 3D addresses, you can use the following functions: AVERAGE, STDEV, STDEV.V, AVERAGE, STDEV, STDEV.Y, SUM, COUNT, COUNT, MIN, MAX, MIN, MAX, VARR, PRODUCT, VARV, VAR.V and DISPA.

If you violate these rules, then you will see some kind of error.

R1C1 format links

This type of links differs from "A1" in that the number is given not only to rows, but also to columns. The developers decided to replace the normal view with this option for convenience in macros, but they can be used anywhere. Here are some examples of such addresses:

• R10C10 is an absolute link to the cell, which is located on the tenth line of the tenth column;
• R - absolute link to the current (in which the formula is indicated) link;
• R[-2] – relative link to the line, which is located two positions above this one;
• R[-3]C – relative reference to the cell, which is located three positions higher in the current column (where you decided to write the formula);
• RC is a relative link to the cell that is five cells to the right and five lines below the current one.

Name usage

The Excel program for naming cell ranges, single cells, tables (regular and summary), constants and expressions allows you to create your own unique names. At the same time, there is no difference for the editor when working with formulas - he understands everything.

You can use the names for multiplication, division, addition, subtraction, calculation of interest, ratios, deviations, rounding, VAT, mortgages, loans, estimates, timesheets, various forms, discounts, salaries, seniority, annuity payments, working with VLOOKUP formulas , "VVD", "INTERIM. RESULTS" and so on. That is, you can do whatever you want.

Only one thing can be called the main condition - you must determine this name in advance. Otherwise, Excel will not know anything about it. This is done in the following way.

1. Select a column.
2. Call the context menu.
3. Select "Give Name".

1. Specify the desired name for this object. In this case, you must adhere to the following rules.

1. Click the "OK" button to save.

In the same way, you can assign a name to any cell, text or number.

You can use the information in the table both with the help of names and with the help of ordinary links. This is what the standard looks like.

And if you try to insert our name instead of the address "D4: D9", you will see a hint. It is enough to write a few characters, and you will see what fits (from the name base) the most.

In our case, everything is simple - “column_3”. And imagine that you will have a large number of such names. You won't be able to memorize everything.

Using functions

There are several ways to insert a function in the Excel editor:

• manually;
• using the toolbar;
• using the Insert Function window.

Let's take a closer look at each method.

In this case, everything is simple - you use your hands, your own knowledge and skills to enter formulas in a special line or directly in a cell.

If you do not have working experience in this area, then it is better to use more lightweight methods at first.

In this case it is necessary:

1. Go to the "Formulas" tab.
2. Click on any library.
3. Select the desired function.

1. Immediately after that, the "Arguments and Functions" window will appear with the function already selected. You just have to put down the arguments and save the formula using the "OK" button.

Substitution Wizard

You can apply it like this:

1. Make any cell active.
2. Click on the "Fx" icon or use the keyboard shortcut SHIFT + F3.

1. Immediately after that, the "Insert Function" window will open.
2. Here you will see a large list of different features sorted by category. In addition, you can use the search if you can not find the desired item.

It is enough to score a word that can describe what you want to do, and the editor will try to display all the appropriate options.

1. Select any function from the proposed list.
2. To continue, you need to click on the "OK" button.

1. You will then be asked for "Arguments and Functions". You can do this manually or simply select the desired range of cells.
2. In order to apply all the settings, you need to click on the "OK" button.

1. As a result of this, we will see the number 6, although this was already understandable, since the Arguments and Functions window displays a preliminary result. The data is recalculated instantly when any of the arguments changes.

Using nested functions

As an example, we will use formulas with logical conditions. To do this, we will need to add some kind of table.

Then follow the instructions below:

1. Click on the first cell. Call the "Insert function" window. Select the "If" function. Click "OK" to paste.

1. Then you will need to compose some kind of logical expression. It must be written in the first field. For example, you can add the values ​​of three cells in one row and check if the sum is greater than 10. In the case of "true", specify the text "More than 10". For a false result - "Less than 10". Then click OK to return to the workspace.

1. As a result, we see the following - the editor gave out that the sum of the cells in the third row is less than 10. And this is correct. So our code is working.

1. Now you need to configure the following cells. In this case, our formula simply extends further. To do this, you first need to move the cursor to the lower right corner of the cell. After the cursor changes, you need to make a left click and copy it to the very bottom.

1. As a result, the editor recalculates our expression for each line.

As you can see, the copy was very successful because we used the relative links we talked about earlier. If you need to fix addresses in function arguments, then use absolute values.

There are several ways to do this: use the formula bar or a special wizard. In the first case, everything is simple - click in a special field and manually enter the necessary changes. But writing there is not very convenient.

The only thing you can do is increase the input field. To do this, just click on the indicated icon or press the key combination Ctrl + Shift + U.

It is worth noting that this is the only way if you do not use functions in the formula.

In the case of using functions, everything becomes much simpler. To edit, follow the instructions below:

1. Make active the cell with the formula. Click on the "Fx" icon.

1. After that, a window will appear in which you can change the arguments of the function you need in a very convenient way. In addition, here you can find out exactly what the result of recalculating the new expression will be.

1. To save the changes made, use the "OK" button.

To remove an expression, just do the following:

1. Click on any cell.

1. Click on the Delete or Backspace button. As a result, the cell will be empty.

You can achieve exactly the same result using the Clear All tool.

Possible errors when compiling formulas in the Excel editor

The following are the most common mistakes that users make:

• The expression uses a huge number of nestings. There should be no more than 64;
• paths to external workbooks are specified in formulas without the full path;
• Opening and closing parentheses are misplaced. That is why in the editor in the formula bar all brackets are highlighted in a different color;

• the names of books and sheets are not taken in quotation marks;
• numbers are used in the wrong format. For example, if you need to specify $2000, you just need to type 2000 and select the appropriate cell format, since the $ symbol is used by the program for absolute references;

• required function arguments are not specified. Note that optional arguments are enclosed in square brackets. Everything without them is necessary for the full operation of the formula;

• Cell ranges are incorrect. To do this, you must use the ":" (colon) operator.

Error codes when working with formulas

When working with a formula, you may see the following error options:

• #VALUE! - This error indicates that you are using the wrong data type. For example, you are trying to use text instead of a numeric value. Of course, Excel can't calculate the sum between two phrases;
• #NAME? - a similar error means that you made a typo in the spelling of the function name. Or you are trying to enter something that does not exist. You can't do that. In addition, the problem may be in another. If you are sure about the function name, then try to look at the formula more closely. Perhaps you forgot a parenthesis. In addition, you need to take into account that text fragments are indicated in quotation marks. If all else fails, try composing the expression again;
• #NUMBER! - displaying such a message means that you have some kind of problem with the arguments or with the result of the formula. For example, the number turned out to be too large or vice versa - small;
• #DIV/0! - This error means that you are trying to write an expression that divides by zero. Excel cannot undo math rules. Therefore, such actions are also prohibited here;
• #N/A! - The editor can show this message if some value is not available. For example, if you use the functions SEARCH, SEARCH, MATCH, and Excel did not find the fragment you are looking for. Or there is no data at all and the formula has nothing to work with;
• If you are trying to calculate something and Excel writes the word #REF!, then the wrong range of cells is used in the function argument;
• #EMPTY! - this error appears if you have an inconsistent formula with overlapping ranges. More precisely, if in reality there are no such cells (which are at the intersection of two ranges). Quite often, this error occurs by accident. It is enough to leave one space in the argument, and the editor will perceive it as a special operator (we talked about it earlier).

When editing the formula (the cells are highlighted) you will see that they do not actually intersect.

Sometimes you can see a lot of # characters that completely fill the width of the cell. In fact, there is no error here. This means that you are working with numbers that do not fit in this cell.

In order to see the value contained there, it is enough to resize the column.

In addition, you can use cell formatting. To do this, you need to follow a few simple steps:

1. Call the context menu. Select Format Cells.

1. Specify the type "General". Use the "OK" button to continue.

Thanks to this, the Excel editor will be able to translate this number into another format that fits in this column.

Formula Usage Examples

The Microsoft Excel editor allows you to process information in any way convenient for you. There are all necessary conditions and opportunities for this. Let's look at some examples of formulas by category. This will make it easier for you to figure it out.

In order to evaluate the mathematical capabilities of Excel, you need to perform the following steps.

1. Create a table with some conditional data.

1. To calculate the amount, enter the following formula. If you only want to add one value, you can use the addition ("+") operator.

1. Oddly enough, in the Excel editor you can not take away using functions. For deduction, the usual operator "-" is used. In this case, the code will be the following.

1. In order to determine how much the first number is from the second as a percentage, you need to use this simple construction. If you want to subtract several values, you will have to write a "minus" for each cell.

Note that the percent symbol is placed at the end, not at the beginning. In addition, when working with percentages, you do not need to additionally multiply by 100. This happens automatically.

1. Excel can add, taking into account several conditions at once. You can calculate the sum of the cells of the first column, the value of which is greater than 2 and less than 6. And the same formula can be set for the second column.

=SUMIFS(B3:B9,B3:B9,">2",B3:B9,"<6") =SUMIFS(C3:C9,C3:C9,">2",C3:C9,"<6")

1. You can also count the number of elements that satisfy some condition. For example, let Excel calculate how many numbers we have greater than 3.

=COUNTIF(B3:B9,">3") =COUNTIF(C3:C9,">3")

1. The result of all formulas will be as follows.

Math functions and graphs

Using Excel, you can calculate various functions and build graphs on them, and then carry out graphical analysis. As a rule, such techniques are used in presentations.

As an example, let's try to build graphs for the exponent and some equation. The instruction will be as follows:

1. Let's create a table. In the first column we will have the original number "X", in the second - the "EXP" function, in the third - the specified ratio. It would be possible to make a quadratic expression, but then the resulting value against the background of the exponent on the graph would practically disappear.

As we said earlier, the growth of the exponent is much faster than that of the usual cubic equation.

Similarly, any function or mathematical expression can be represented graphically.

Everything described above is suitable for modern programs of 2007, 2010, 2013 and 2016. The old Excel editor is significantly inferior in terms of features, number of functions and tools. If you open the official help from Microsoft, you will see that they additionally indicate in which version of the program this function appeared.

In all other respects, everything looks almost exactly the same. As an example, let's calculate the sum of several cells. For this you need:

1. Specify some data for calculation. Click on any cell. Click on the "Fx" icon.

1. Select the "Math" category. Find the "SUM" function and click on "OK".
1. You can try to recalculate in any other editor. The process will be exactly the same.

Conclusion

In this tutorial, we talked about everything related to formulas in the Excel editor, from the simplest to the very complex. Each section was accompanied by detailed examples and explanations. This is done so that the information is accessible even for complete dummies.

If something does not work out for you, then you are making a mistake somewhere. Perhaps you have typos in expressions or incorrect cell references. The main thing to understand is that everything needs to be driven in very carefully and carefully. Moreover, all functions are not in English, but in Russian.

In addition, it is important to remember that formulas must begin with the symbol "=" (equal to). Many novice users forget about it.

Sample File

In order to make it easier for you to deal with the formulas described earlier, we have prepared a special demo file in which all the above examples were compiled. You can do it from our site absolutely free. If during training you use a ready-made table with formulas based on the data you filled in, you will achieve results much faster.

Video instruction

If our description did not help you, try watching the attached video below, which tells the main points in more detail. Perhaps you are doing everything right, but you are missing something. With the help of this video, you should deal with all the problems. We hope these tutorials have helped you. Check us out more often.

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.

CONNECT

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

LOWERLINE

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

CAPITAL

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

PROPER

Text function PROPER 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

LEFT and RIGHT

Text functions LEFT And 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 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 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.

FIND

Text function 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 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 function 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:

REPLACE

Replaces characters located at a known location in a string with the desired value. In excel text function REPLACE 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 will 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!

Quite often, when working in Excel, there is a need to insert explanatory text next to the result of calculating a formula, which makes it easier to understand this data. Of course, you can select a separate column for explanations, but not in all cases, adding additional elements is rational. However, in Excel there are ways to put formula and text in the same cell together. Let's see how this can be done using various options.

If you just try to insert text into one cell with a function, then with such an attempt, Excel will display an error message in the formula and will not allow you to make such an insert. But there are two ways to still insert text next to the formula expression. The first one is to use the ampersand, and the second one is to use the function CONNECT.

Method 1: Using an ampersand

The easiest way to solve this problem is to use the ampersand symbol ( & ). This sign produces a logical separation of the data that the formula contains from the text expression. Let's see how this method can be applied in practice.

We have a small table in which the fixed and variable costs of the enterprise are indicated in two columns. The third column contains a simple addition formula that sums them up and outputs a grand total. We need to add an explanatory word after the formula in the same cell where the total cost is displayed "rubles".

Naturally, it is not necessary to do all of these steps. We just showed that with a normal input without a second ampersand and quotes with a space, the formula and text data will merge. You can set the correct space even when performing the second paragraph of this guide.

When writing text before a formula, we adhere to the following syntax. Immediately after the "=" sign, open the quotes and write the text. After that, close the quotes. We put the ampersand sign. Then, if you need to enter a space, open the quotes, put a space and close the quotes. We click on the key Enter.

To write text along with a function, and not with a regular formula, all the steps are exactly the same as described above.

Method 2: Using the CONCATENATE Function

You can also use the function to insert text along with the result of calculating the formula CONNECT. This operator is designed to combine in one cell the values ​​displayed in several elements of the sheet. It belongs to the category of text functions. Its syntax is the following:

CONCATENATE(text1, text2, ...)

In total, this operator can have from 1 before 255 arguments. Each one represents either text (including numbers and any other characters) or links to cells that contain it.

Let's see how this function works in practice. For example, let's take the same table, just add one more column to it "Total cost" with an empty cell.

1. Select an empty column cell "Total cost". Click on the icon "Insert Function" located to the left of the formula bar.



2. Activation in progress Function Wizards. Moving to category "Text". Next, select the name "CONNECT" and click on the button OK.



3. Operator Arguments window launches CONNECT. This window consists of fields named "Text". Their number reaches 255 , but for our example, we only need three fields. In the first one we will place the text, in the second we will place a link to the cell containing the formula, and in the third we will place the text again.

   Set the cursor in the field "Text1". Put in the word "Total". You can write text expressions without quotes, as the program will put them down itself.

   Then we go to the field "Text2". Set the cursor there. We need to indicate here the value that the formula displays, which means that we should give a link to the cell containing it. This can be done by simply entering the address manually, but it is better to place the cursor in the field and click on the cell containing the formula on the sheet. The address will be displayed in the arguments box automatically.

   In field "Text3" enter the word "rubles".

   After that, click on the button OK.



4. The result is displayed in a pre-selected cell, but, as we can see, as in the previous method, all values ​​are written together without spaces.



5. In order to solve this problem, again select the cell containing the operator CONNECT and go to the formula bar. There, after each argument, that is, after each semicolon, add the following expression:

   There must be a space between the quotes. In general, the following expression should appear in the function line:

   CONCATENATE("Total";" ";D2;" ";"rubles")

   We click on the key ENTER. Now our values ​​are separated by spaces.



6. You can hide the first column if you want. "Total cost" with the original formula so that it does not take up extra space on the sheet. Just removing it will not work, as this will break the function CONNECT, but it is quite possible to remove the element. Click the left mouse button on the sector of the coordinate bar of the column that should be hidden. After that, the entire column is highlighted. Click on the selection with the right mouse button. The context menu is launched. Select an item in it. "Hide".



7. After that, as we see, the column we do not need is hidden, but the data in the cell in which the function is located CONNECT displayed correctly.

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 large and small, or change only the first letter in a word to capital.

Formulas with Excel Text Functions

First, let's take an example of 3 Excel text functions:

1. UPPER - This text function changes all letters in a word to uppercase, uppercase.
2. LOWER - This function converts all text characters to lowercase, small letters.
3. PROPER - the function changes only the first letter in each word to capital, large.

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

Despite such a wide range of functions, Excel still needs a function that can capitalize the first letter 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 LONG.

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

If you look closely at the syntax of the above formula, it is easy to replace that it consists of two parts connected by the & operator.

The left side of the formula uses the additional LEFT function:

The task of this part of the formula is to change the first letter to capital 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:

1. Text - a link to a cell with the original text.
2. Number of_characters - the number of returned characters from the left side (from the beginning) of the source text.

In this example, you only need to get the first 1 character from the original text string in cell A1. The resulting character is then converted to an uppercase capital letter.

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 to small letters. But you need to do this 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 received from the right side of the source text. However, 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. In addition, the length of different source text strings may differ. Therefore, we need to pre-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 converted to the user's requirements. Therefore, it should not be affected by any function from the right side of the formula.

To automatically calculate the length of the source text, the Excel text function - DLSTR (deciphered as the length of the string) is used. This function requires only one argument to fill in - a link to the source text. As a result of the calculation, it returns a numeric value, after the function =LSTR(A1) we subtract -1. Which gives us the opportunity not to affect the first capital letter with the right side of the formula. As a result, the RIGHT function returns a text string without one first character for the LOWER function, which changes all text characters to small lowercase letters.

As a result of combining 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 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.