As we all know, Excel has very powerful data processing capabilities, including text processing. Today, the leader will share with you the meaning and application of our commonly used text functions.
#1. Extract characters from text
Left function: Returns the first or previous character in the text based on the specified number of characters. Several characters, that is, counting from the left, extract several characters from the text.
Right function: Returns the last character or characters in the text according to the specified number of characters, that is, extracts several characters from the right.
Mid function: Returns a specific number of characters starting from the specified position in the text, that is, extracting several characters starting from a specified position in the middle.
Let’s give a simple example of the application of these three text functions in character extraction, as shown below:
2. In text Character search
Find function and Search function: locate a string in the text, starting from the specified position, and return the position of the search string in the text. The application of these two text functions is shown in the figure below:
We can also see from the example: Both the Find function and the Search function can accurately locate the string in the text. location, but there are also differences between the two functions:
The Find function is case-sensitive, while the Search function is not.
The Search function supports wildcards, but the Find function does not.
3. Character conversion
Upper function: Change the specified text to uppercase
Lower function: Change the specified text to lowercase
Proper function: Change the first letter of the specified text to uppercase
The application of these three text functions is as shown in the figure below:
4. Substitute characters in the text
The Substitute function and the Replace function are both used to replace a certain string in the text. The difference is that the Substitute function replaces a specified string in the text. The Replace function replaces a string at a specified position in the text. In short, one is replaced according to the specified text, and the other is replaced according to the specified position. Their applications are shown in the figure below:
5. Text merging
Phonetic function and Concatenatet function (the 2016 version is called Concat function) can combine text in multiple cells. The difference is that the Phonetic function is not applicable to formula results, numerical values, dates and times, etc. The application is as shown in the figure below:
6. Repeated display of characters
Rept function: Repeat the display of specified characters in the text. As shown in the figure below:
7. Text length calculation
Len function: Calculate the text length, that is, the number of characters. Regardless of whether it is Chinese, English or numbers, it is counted as 1 character.
Lenb function: Calculate the number of bytes of text. The punctuation marks input in Chinese and full-width states are 2 bytes each. The punctuation marks input in English, numeric and half-width states are calculated as 1 byte.
The application of these two text functions is shown in the figure below:
Extension: Len function and Lenb function can be combined for mixed Chinese and English texts ,Computation and extraction of Chinese content.
8. Character cleaning in text
The two data beautification functions, Clean function and Trim function, are mainly used to clean spaces and some special characters in text. Cleaning and deletion, we discussed in the previous article "You don't know these two Excel beauty functions, how can you talk about data processing?" 》This article has introduced it in detail. You can click on the link to view it directly. I won’t go into details here.
9. Character format conversion in this article
Text function: can convert a numerical value into text represented by a specified numerical format. It can be said to be the most varied text function. Function has a certain mysterious color. The main applications of the Text function are shown in the figure below:
For the number format here, you can refer to the various number formats in text form under the Number tab in Cell Format.
In addition, we can also use the Text function to grade the data: for example, if we divide student test scores into good, passing, and failing, enter the formula: =TEXT(A2,"[>=85]Good ;[>=60,
For more Excel-related technical articles, please visit theExcel Basic Tutorialcolumn to learn!
The above is the detailed content of What are the excel string functions?. For more information, please follow other related articles on the PHP Chinese website!