home › Firmware › Text functions in excel formulas: uppercase, lowercase and propn. Examples of text functions in Excel Any text value in excel

Text functions in excel formulas: uppercase, lowercase and propn. Examples of text functions in Excel Any text value in excel

Full name, numbers bank cards, addresses of clients or employees, comments and much more - all these are lines that many encounter when working with Excel application. Therefore, it is useful to be able to process this type of information. This article will look at text functions in Excel, but not all, but those that, in the opinion of the site, are the most useful and interesting:

You can find a list of all text functions on the “Formulas” tab => “Text” drop-down list:

LEFT function

Returns a substring from text in left-to-right order within a given number of characters.

Syntax:=LEFT(text, [number of characters])

Argument definitions:

text – a string or a reference to a cell containing text from which you want to return a substring;
number_characters is an optional argument. An integer indicating how many characters to return from the text. Defaults to 1.

Usage example:

Formula: =LEFT("Custom text";8) – the returned value "Custom".

RIGHT function

This function similar to the LEFT function, except that characters are returned from the end of the string.

Usage example:

Formula: =RIGHT("free text";5) – the returned value is "text".

LENGTH function

It is used to determine the length of the string. The result is an integer indicating the number of characters of text.

Syntax:=LENGTH(text)

Usage example:

FIND function

Returns a number that is the occurrence of the first character of the substring of the search text. If the text is not found, the error “#VALUE!” is returned.

Syntax:=FIND(search_text; search_text; [start_position])

Argument definitions:

search_text – the string to be found;
search_text – text in which the first argument is searched;
start_position is an optional element. Accepts an integer that specifies at which text_to_search character the search should begin. Defaults to 1.

Usage example:

From an excerpt of a poem by the great poet S.A. Yesenin we find the occurrence of the first character of the line “bird”. The search is carried out from the beginning of the line. If in the above example the search was carried out from character 40, then the function would return an error, because no entry position was found.

REPLACE function

This function replaces part of a string in a specified number of characters, starting with the specified character, with new text.

Syntax: REPLACE(old_text, starting_position, number of characters, new_text)

Argument definitions:

old_text – a string or a link to a cell containing text;
start_position – the serial number of the character from left to right from which to replace;
number_characters – the number of characters, starting from start_position inclusive, that need to be replaced with new text;
new_text – a string that replaces part of the old text specified by the arguments start_position and number of characters.

Usage example:

Here, in the line contained in cell A1, the word “old”, which begins at the 19th character and is 6 characters long, is replaced with the word “new”.

In the case of processing a small number of cells, this method is quite acceptable. But if you need to process several thousand different rows, then the process of creating formulas will become labor-intensive. Therefore, we will redo the considered example, using knowledge of the functions that have already been described at the beginning of the article, namely:

The “start_position” argument will be replaced by the “FIND” function;
In place of the argument “number of characters” we will insert the function “LENGTH”.

As a result, we get the formula: =REPLACE(A1; FIND("old";A1);LENGTH("old");"new")

Look at the print screen of the function window. The returned results of the arguments have not changed, but now they can be determined dynamically by composing the formula once.

SUBSTITUTE function

This function replaces occurrences of the specified substring in the text with new text, which is similar to the “REPLACE” function, but there is a fundamental difference between them. If the "REPLACE" function changes the text specified character by character manually, then the "SUBSTITUTE" function automatically finds occurrences of the specified string and changes them.

Syntax: SUBSTITUTE(text; old_text; new_text; [occurrence_number])

Argument definitions:

old_text – substring from the first argument that needs to be replaced;
new_text – a string to replace the old text;
occurrence_number is an optional argument. Accepts an integer indicating the sequence number of the old_text occurrence that is to be replaced; all other occurrences will not be affected. If you leave the argument empty, all occurrences will be replaced.

Usage example:

The line in cell A1 contains text that has 2 substrings “old”. We need to substitute the string “new” in place of the first occurrence. As a result, part of the text “...old-old...” is replaced with “...new-old...”.

If the last argument were omitted, the result would be the string "a string containing new-new text".

PSTR function

PSTR returns from the specified string a portion of the text in a specified number of characters, starting from the specified character.

Syntax: PSTR(text; start_position; number_of_characters)

Argument definitions:

text – a string or a link to a cell containing text;
start_position – the serial number of the character from which the string should be returned;
number_characters – a natural integer indicating the number of characters to be returned, starting from position start_position.

Usage example:

From the text located in cell A1, you need to return the last 2 words, which have a total length of 12 characters. The first character of the returned phrase has sequence number 12.

The number_characters argument may exceed the allowed length of returned characters. Those. if in the considered example, instead of number_characters = 12, the value 15 had been specified, then the result did not change, and the function also returned the string “PSTR function”.

For ease of use of this function, its arguments can be replaced by the “FIND” and “LENGTH” functions, as was done in the example with the “REPLACE” function.

SPACE function

This function removes all extra spaces: spaces around the edges and double spaces between words. After the function processes the strings, only single spaces between words are left.

Syntax:=SPACE(text)

Usage example:

SPACE( "Text with extra spaces between words and around the edges" )

The result of executing the function will be the line: "Text with extra spaces between words and around the edges" .

CONNECT function

Using the CONCATENATE function, you can combine several lines together. The maximum number of rows to merge is 255.

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

The function must contain at least one argument

Here are the most commonly used Excel text functions (quick reference). Additional information about functions can be found in the function wizard dialog box, as well as in Excel help system.

Text functions convert numeric text values to numbers and numeric values to character strings (text strings), and also allow you to perform various operations on character strings.

TEXT function

The TEXT function converts a number into a text string with a specified format. Syntax:

=TEXT(value,format)

The value argument can be any number, formula, or cell reference. The format argument determines how the returned string is displayed. You can use any of the formatting characters except the asterisk to set the required format. The use of the General format is not allowed. For example, the following formula returns the text string 25,25:

=TEXT(101/4,"0.00")

RUBLE function

The DOLLAR function converts a number to a string. However, RUBLE returns a string in currency format with the specified number of decimal places. Syntax:

=RUBLE(number, number_characters)

Excel will round the number if necessary. If the number_characters argument is omitted, Excel uses two decimal places, and if this argument is negative, the returned value is rounded to the left of the decimal point.

LENGTH function

The LEN function returns the number of characters in a text string and has the following syntax:

=LENGTH(text)

The text argument must be a character string enclosed in double quotes, or a cell reference. For example, the following formula returns the value 6:

=LENGTH("head")

The LENGTH function returns the length of the displayed text or value, not the cell's stored value. In addition, it ignores leading zeros.

CHARACTER and CHARACTER CODE function

Any computer uses numeric codes to represent characters. The most common character encoding system is ASCII. In this system, numbers, letters and other symbols are represented by numbers from 0 to 127 (255). The CHAR and CODE functions deal specifically with ASCII codes. The CHAR function returns the character that matches the specified ASCII numeric code, and the CHAR function returns the ASCII code for the first character of its argument. Function syntax:

=CHAR(number)
=CODECHIM(text)

If you enter a character as a text argument, be sure to enclose it in double quotes; otherwise, Excel will return an incorrect value.

Functions SPACEBEL and PECHSIMV

Often leading and trailing spaces prevent values from being sorted correctly in a worksheet or database. If you use text functions to work with worksheet text, extra spaces can prevent formulas from working correctly. The TRIM function removes leading and trailing spaces from a string, leaving only one space between words. Syntax:

=SPACE(text)

The CLEAN function is similar to the SPACE function except that it removes all non-printing characters. The PREPCHYMB function is especially useful when importing data from other programs because some imported values may contain non-printing characters. These symbols may appear on worksheets as small squares or vertical bars. The PRINTCHARACTERS function allows you to remove non-printing characters from such data. Syntax:

=PECHSIMB(text)

COINCIDENT function

The EXACT function compares two strings of text for complete identity, taking into account the case of letters. Differences in formatting are ignored. Syntax:

=COINCIDENT(text1,text2)

If the arguments text1 And text2 case-sensitive, the function returns TRUE, otherwise FALSE. Arguments text1 And text2 must be character strings enclosed in double quotes, or references to cells that contain text.

UPPER, LOWER, and PROP functions

Excel has three functions that let you change the case of letters in text strings: UPPER, LOWER, and PROPER. The CAPITAL function converts all letters in a text string to uppercase and the LOWER function converts all letters to lowercase. The PROPER function capitalizes the first letter of each word and all letters immediately following non-letter characters; all other letters are converted to lowercase. These functions have the following syntax:

=UPPERCASE(text)
=LOW(text)
=PROPNACH(text)

When working with existing data, quite often a situation arises when you need to modify the original values themselves to which text functions are applied. You can enter the function in the same cells where these values are located, since the entered formulas will replace them. But you can create temporary formulas with a text function in empty cells on the same row and copy the result to the clipboard. To replace the original values with modified values, select the original text cells, select Paste Special from the Edit menu, select the Values radio button, and click OK. You can then delete the temporary formulas.

Excel is essential when you need to organize, process, and save a lot of information. It will help automate calculations, making them easier and more reliable. Formulas in Excel allow you to carry out arbitrarily complex calculations and get results instantly.

How to write a formula in Excel

Before learning this, there are a few basic principles to understand.

Each one begins with an “=” sign.
Cell values and functions can participate in calculations.
Operators are used as familiar mathematical symbols for operations.
When you insert a record, the cell displays the result of the calculation by default.
You can view the design in the line above the table.

Each cell in Excel is an indivisible unit with its own identifier (address), which is indicated by a letter (column number) and a number (row number). The address is displayed in the field above the table.

So how do you create and paste a formula in Excel? Proceed according to the following algorithm:

Designation Meaning

Addition
- Subtraction
/ Division
* Multiplication

If you need to specify a number rather than a cell address, enter it from the keyboard. To specify a negative sign in an Excel formula, click "-".

How to enter and copy formulas in Excel

They are always entered after clicking on “=”. But what to do if there are many similar calculations? In this case, you can specify one and then simply copy it. To do this, enter the formula and then “stretch” it in the desired direction to multiply it.
Place the pointer on the cell to be copied and move the mouse pointer to the lower right corner (over the square). It should take the form of a simple cross with equal sides.

Click left button and pull.

Release when you need to stop copying. At this moment, the calculation results will appear.

You can also stretch it to the right.
Move the pointer to an adjacent cell. You will see the same entry, but with different addresses.

When copying in this way, the row numbers increase if the shift occurs downward, or the column numbers increase if it is shifted to the right. This is called relative addressing.
Let's enter the VAT value into the table and calculate the price including tax.

The price including VAT is calculated as price*(1+VAT). Let's enter the sequence into the first cell.

Let's try to copy the entry.

The result was strange.

Let's check the contents in the second cell.

As you can see, when copying, not only the price, but also the VAT shifted. And we need this cell to remain fixed. Let's anchor it with an absolute link. To do this, move the pointer to the first cell and click on address B2 in the formula bar.

Press F4. The address will be appended with a “$” sign. This is the sign of an absolute cell.

Now after copying the B2 address will remain unchanged.
If you accidentally entered data into the wrong cell, simply move it. To do this, move the mouse pointer over any border, wait until the mouse looks like a cross with arrows, press the left button and drag. At the desired location, simply release the manipulator.

Using functions for calculations

Excel offers a large number of functions, which are divided into categories. You can view the full list by clicking on the Fx button next to the formula line or opening the “Formulas” section on the toolbar.

Let's talk about some functions.

How to Set "If" Formulas in Excel

This function allows you to set a condition and perform a calculation depending on its truth or falsity. For example, if the quantity of goods sold is more than 4 packs, you should purchase more.
To insert the result depending on the condition, we will add another column to the table.

In the first cell under the heading of this column, set the pointer and click the “Boolean” item on the toolbar. Let's select the "If" function.

As with inserting any function, a window will open for you to fill in the arguments.

Let's indicate the condition. To do this, click on the first row and select the first cell “Sold”. Next, put the “>” sign and indicate the number 4.

In the second line we will write “Purchase”. This message will appear for those items that have been sold out. The last line can be left blank since we have no action if the condition is false.

Click OK and copy the entry for the entire column.

To prevent “FALSE” from being displayed in the cell, let’s open the function again and correct it. Place the pointer on the first cell and press Fx next to the formula bar. Insert the cursor on the third line and put a space between the quotes.

Then OK and copy again.

Now we see which product should be purchased.

Formula text in Excel

This function allows you to apply a format to the contents of a cell. In this case, any type of data is converted to text, which means it cannot be used for further calculations. Let's add a column to format the total.

In the first cell, enter a function (the “Text” button in the “Formulas” section).

In the arguments window, we will indicate a link to the total amount cell and set the format to “#rub.”

Click OK and copy.

If we try to use this amount in calculations, we will receive an error message.

"VALUE" indicates that the calculation cannot be performed.
You can see examples of formats in the screenshot.

Date formula in Excel

Excel provides many options for working with dates. One of them, DATE, allows you to construct a date from three numbers. This is convenient if you have three different columns - day, month, year.

Place the pointer on the first cell of the fourth column and select a function from the Date and Time list.

Arrange the cell addresses accordingly and click OK.

Copy the entry.

AutoSum in Excel

In case you need to add up a large number of data, Excel provides the SUM function. For example, let's calculate the amount for goods sold.
Place the pointer in cell F12. It will calculate the total.

Go to the Formulas panel and click AutoSum.

Excel will automatically highlight the closest number range.

You can select a different range. In this example, Excel did everything correctly. Click OK. Pay attention to the contents of the cell. The SUM function was substituted automatically.

When inserting a range, the first cell address, a colon, and the last cell address are specified. ":" means "Take all cells between the first and last. If you need to list multiple cells, separate their addresses with a semicolon:
SUM (F5;F8;F11)

Working in Excel with formulas: example

We told you how to make a formula in Excel. This is knowledge that can be useful even in everyday life. You can manage your personal budget and control expenses.

The screenshot shows the formulas that are entered to calculate the amounts of income and expenses, as well as the calculation of the balance at the end of the month. Add sheets to the workbook for each month if you don't want all the sheets on one. To do this, simply click on the “+” at the bottom of the window.

To rename a sheet, double-click it and enter a name.

The table can be made even more detailed.
Excel is very useful program, and calculations in it provide almost unlimited possibilities.

Have a great day!

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) – a very useful function, 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 returning characters with last character strings;
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 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

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 3 Excel text functions as an example:

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 to each other by the & operator.

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

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 this example, you only need to get the first 1 character from the original text string in cell A1. Next, the resulting character is converted to a capital uppercase 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 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 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.

Popular in the category: