In the lines of the program Microsoft Office Exel, you can enter any information that users need to successfully complete the tasks. To work most productively, you need to be able to deal with data in rows. This article will cover examples of text functions in Excel.
To find them in the program, go to the tab called “Formulas”, open the list and select “Text”.
CONNECT
This function allows the user to combine several lines between themselves. The maximum number allowed for the connection reaches 255. Please note that you need to put spaces here yourself. The body of "CONNECT" must contain at least one argument. Consider an example of its recording and action:
- = CLICK ("Argument1"; ""; "Argument2");
- The result is the following line: "Argument1 Argument2".
An alternative to this function for working with text is the & character, 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 starts with the marked character. Key positions (text, starting position, number of characters). Each of them is defined as follows:
- The text means a line or a link to a cell containing the necessary information.
- The starting position marks the number of the character from which the line will be returned.
- The number of characters is a natural integer indicating the amount of characters to be returned starting from the starting position.
An example is the following situation. Cell A1 contains some text. It is necessary to return the last two words, the total length of the characters is 12. The character that must be returned in a particular phrase is listed under serial number 12.
SUBSTITUTE
It replaces the finished text with the required one in the place specified by the user. Something like a function called "Replace." But details about it will be presented later. The main difference between this option is that it automatically calculates the occurrences of the required string and performs a replacement.
The specific function looks as follows: text; old text new text; entry number.
Let's analyze each of the arguments:
- The text refers to a link to a specific cell or row that contains this same 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 change the old data variant.
- The entry number is an optional argument to this text function in Excel. When specified, only matching occurrences will be replaced. If it is absent, the change will occur everywhere.
REPLACE
Performs the function of replacing part of a string in a specific number of characters. The process begins with a character, which is indicated on the account, to the new text. It looks as follows: old text; starting position; number of signs; new text.
Let's consider individually each of the arguments of this text function in Excel:
- A link to a specific cell or row containing previously entered information is called Old Text.
- The starting position indicates the number of the symbol 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 set by the Start position and the Number of characters.
Now let's look at an example of using a text function in Excel. Cell A1 contains a row with the word "old." It starts with the 19th character and has a duration of six identical units. Replacing 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 fine. But what about the situation when you have to replace among several thousand lines? In this case, you should turn to two other Excel text functions, which will be discussed a little later:
- The initial position will be replaced by "FIND".
- The number of characters will change to "DLSTR".
DLSTR
This text function in Excel helps the user determine the length of the string. The result of her work will be the return of an integer showing the volume of characters in the text. It looks like this: = DLSTR (text).
"TO FIND"
When working with text, it performs the function of returning a number, which is the occurrence of the first character of the required text in the substring. In the event that no data was found, instead of a number, the error "#VALUE!" Is returned.
Here is its form in the document. But without specific arguments: = FIND (search text; search text; start position).
Now we will consider separately all the arguments given:
- The required line is the text to be searched.
- The data among which the first argument is searched is the Text to search.
- Starting position is not a required element. By default, it has a value of one.