Excel Text Functions

Excel tables contain mostly numerical data on which you can perform various mathematical, logical and other operations. At the same time, a considerable part of the tables contains cells with text. For example, price lists or customer lists with addresses certainly include test data. In order to perform various transformations on this data, Excel text functions exist. These functions greatly facilitate the processing of text data. It is important not only to know about the existence of these functions and how they work, but also to be able to apply this knowledge in practice.
LEN function Excel
REPLACE and SUBSTITUTE function in Excel
LEFT and RIGHT function in Excel
FIND and SEARCH function in Excel
TRIM and CLEAN function in Excel
REPT function in Excel
Excel MID function
CONCATENATE function in Excel

LEN function Excel

Using the LEN function, you can count the number of text characters in a cell (the length of a string). When using it, it should be taken into account that this number includes not only the number of letters in all words, but also spaces between words and even spaces before and after these words. Text function LEN
To use this function, click on the cell where the string length will be displayed. In the Excel main menu, open the “Formulas” tab and select “Text”. In the list of functions that appears, select the LEN function. LEN Excel
In the “Function Arguments” window, specify the address of the cell in which the number of characters will be counted. Excel string length
After clicking the “OK” button, the number of characters in the specified line will appear in the cell with the result of the function.

The LEN function is convenient to use in cases where the number of characters in a string is limited. For such rows, you can quickly calculate the number of characters in each cell of the selected column and highlight the amounts in excess of the specified limit.

REPLACE and SUBSTITUTE function in Excel

The REPLACE function, judging by the name, changes the text in the cell to the specified text. For example, you need to specify only the first and last characters in the column with passwords of the same length, and mark the rest with asterisks. How to do it?

  1. Place the cursor in the cell where the result of replacing characters will be displayed. In the Excel main menu on the “Formulas” tab, click on the “Text” item and select the “REPLACE” function.
  2. In the function arguments window, specify the necessary parameters:
    • “Old_text”. Click the cell whose text you want to convert, or manually enter the cell address.
    • “Start_num”. Indicate from which position the text should be replaced.
    • “Num_chars”. Indicate how many characters should be replaced in the text. If you put a zero, the selected text will be inserted from the specified position.
    • “New_text”. Specify text to replace characters. REPLACE function
  3. Click “OK”.
  4. Pull on the corner of the first cell to copy the formula for the next cells. REPLACE Excel

The REPLACE function is rarely used, but in some cases its use is justified. For example, when in a line you need to insert a new text.

Along with the REPLACE function for similar purposes, you can use the SUBSTITUTE function. This function replaces the specified text with the specified value. For example, in the cell with the text “current month” you need to replace the word “month” with the word “quarter”. Select the SUBSTITUTE function and enter the appropriate arguments. Function SUBSTITUTE
After clicking the “OK” button in the specified cell, the phrase “current month” will be replaced with “current quarter”.

What is the difference between these functions? In the function SUBSTITUTE, it is explicitly indicated which text should be replaced with the given one, while to use the REPLACE function, it is necessary to indicate only the initial position for replacing the text and the number of characters to be replaced. Thus, in some cases it is easier to use the function SUBSTITUTE than the function REPLACE.

In addition, the REPLACE function corrects only one occurrence of a given phrase, and the SUBSTITUTE function can replace both all occurrences of a given word, and only the first, second, and so on. However, keep in mind that the function SUBSTITUTE is case sensitive. That is, in the given example, if the word “Month” were written with a capital letter, the replacement would not have occurred.

LEFT and RIGHT function in Excel

The functions LEFT and RIGHT extract from the text line the specified number of characters and place them in the selected cell. The LEFT function returns the specified number of characters, starting with the first character of the line, and the RIGHT function returns the specified number of characters, starting with the last character in the line. In the first case, the counting of signs goes from left to right, and in the second – from right to left.

Consider an example. There is a table in one of the columns of which contains the price along with the currency code. It is necessary to carry out currency codes in a separate column. For this purpose, you can use the function RIGHT.

  1. Place the cursor in the first cell of the column in which the currency codes will be located. In the “Formulas” tab, select “Text” and in the list of functions that appears, select the option RIGHT.
  2. In the “Function Arguments” window that opens, specify the address of the first cell in the column from which you want to extract the currency code, and specify the number of characters that you want to copy, starting with the last character of the line. Function RIGHT
  3. Click “OK”.
  4. Pull the lower right corner of the first cell of the currency column to copy the formula for the remaining cells. Function LEFT

Often the functions LEFT and RIGHT are used in combination with other functions. For example, using them in conjunction with the SEARCH function allows you to select the desired values for a given criterion.

FIND and SEARCH function in Excel

The purpose of these functions becomes clear already by their name. Both of these functions analyze the text string and give the ordinal number of the position from which the search text begins, specified as an argument.

Consider an example. In the line “Computer chair” you need to find the position number from which the word “chair” begins. For this purpose we will use the FIND function.

  1. Place the cursor in the cell where the search result will be displayed. In the “Formulas” tab, go to the “Text” section and in the appeared list of functions select the function FIND.
  2. Enter function arguments:
    • Fnd_text – “chair”.
    • Within_text – A1 (cell address with text string).
    • Start_position – can be omitted. In this case, the search will be performed starting from the first character. The position number must be specified when in the text of the line the search text is found several times.
  3. Click “OK”.
  4. As a result of these actions, the position number with which the word “chair” begins appears in the cell with the result. FIND function

If the specified text is not found, the error message “#VALUE!” Will appear in the cell.

What is the difference between the SEARCH and FIND functions? The FIND function, unlike the SEARCH function, is case-sensitive. That is, if in this example, as an argument, specify the word “Chair” with a capital letter, it will not be found.

At the same time, the SEARCH function, in contrast to the FIND function, allows using wildcards. These include, for example, an asterisk (*) and a question mark (?). The first character indicates any test, and the second character – any character. That is, in this example, for the “с???r” argument in the first cell, the SEARCH function will return the value 10, while the FIND function will indicate that the value was not found.

TRIM and CLEAN function in Excel

The TRIM and CLEAN functions are designed to clear text from unnecessary characters. Extra spaces and unprintable characters often arise when copying text from other programs. Non-printing characters appear, for example, in the form of squares or dashes. To delete them, position the cursor in the cell where the corrected text will be located, and select the CLEAN function. As a function argument, specify the address of the cell with non-printable characters. CLEAN function
After pressing the “OK” button, text without unnecessary characters will appear in the free cell.

In the same way, the TRIM function is used, which removes extra spaces in the text, including at the beginning and at the end of a line, leaving them only one between the words. Often this function is used to avoid errors when sorting cell values or to avoid errors in formulas.

The algorithm of using this function is exactly the same as that of the TRIM program. TRIM function

REPT function in Excel

The REPT function displays the specified text or symbol a specified number of times. This function can be used as part of complex formulas, or separately. It is often used to hide a given text and replace it with a character set.
Consider an example when in the table with user account data you need to replace the passwords set explicitly with an asterisk (*).

  1. Place the cursor in the first cell of the column where the passwords will be located. On the “Formulas” tab in the “Text” section, select the REPT function.
  2. In the “Function Arguments” window, in the “Text” field, enter an asterisk (*). In the “Number_times” field, specify the LEN(B1), which will count the number of characters in each password and display as many asterisks as there are characters in the password. REPT function
  3. Click “OK” and copy the resulting formula to the bottom cells of the column.REPT in Excel

The REPT function can also be used in cases when you need to hide only a part of the text or add some text or a symbol to the original cell data. This can be implemented in conjunction with other functions, such as RIGHT, LEFT, IF, LEN and others.

Excel MID function

Using the MID function, you can extract a fragment of a text string and place it in a separate cell. For the successful operation of this function, it is enough to enter the address of the cell from which you need to extract part of the text, as well as indicate the starting position of the extracted fragment and the number of characters.

The convenience of using this function can be clearly seen in the following example. The table has a column with the names of Samsung phones. It is necessary to bring the part of the name after the words “Samsung Galaxy” into a separate column.

  1. Place the mouse cursor in the column where the results of the function will be placed, and in the list of text functions select the MID function.
  2. In the function arguments window that appears, in the “Text” field, specify the address of the cell from which the text will be extracted. In the “Start_num” field, specify the position from which the extracted text begins (taking into account the spaces between words). In the “Number_chars” field, enter the number of characters to be extracted.MIND function
  3. After pressing the “OK” button, the necessary text fragment will be placed in the specified cell.

Unfortunately, most often the number of extracted characters in the cells of a column is not the same, so the MID function is recommended to be used in conjunction with the FIND function and search by some criterion, for example, by the presence of a space between words. Extract text from Excel line
In this example, the formula for the first cell of the column with the results will be: =MID(A1;FIND(” “;A1)+1;255). In this formula, “+1” means that the position of the text to be searched must also be taken into account, but it should not be returned.

CONCATENATE function in Excel

With the help of the CONCATENATE function you can connect several text lines into one line. There can be a lot of connected lines – up to 255. You can use text, numbers or cell addresses as function arguments.

Consider an example in which the name and surnames of people in separate cells need to be displayed in one cell.

  1. Place the mouse cursor in the cell where the result of the function will be located.
  2. Go to the text functions section and select the CONCATENATE function.
  3. In the function arguments window that opens, alternately specify the addresses of the cells being joined. Do not forget about the spaces between words. Spaces are indicated in quotes.CONCATENATE function
  4. After pressing the “OK” button, a text consisting of the first and last names will appear in the cell with the result of the function. Connect Excel Rows

If the argument is a text value, not a cell address, it must be enclosed in quotes.