As shown in the figure below, when the data to be extracted is contained after the string and the number of characters of the data to be extracted is not constant, the RIGHT function alone cannot be used to extract the data.
."A2The cell's "$20Function to extract after the "+" mark
=RIGHT(A2,LEN(A2)-FIND("\",A2))In the following sections, we will explain the concept of this function.
Before specific characterThe method to extract theHow to extract characters before a specific character "LEFT and FIND functions" Excel (Excel).
How to use the RIGHT function
The RIGHT function is a function that extracts XX characters from the right side of a cell string.
= RIGHT(string,[number of characters])
Both full-width and half-width characters are counted as one character.
This time, in the state shown in the above figure, the "A2right end from the "" cell.3In order to extract the amount of characters, the "string" must be "A2cell, the "number of characters" is3characters, so enter the following
=RIGHT(A2,3)Then, the amount could be displayed as shown in the figure below.
However, when this function was applied to the cells below as well, the "B4" cell displayed the characters to the left of the amount, and the "B5" cell displayed only the last three digits "600" of "15,600," as shown in the figure below.
This is because the amount in each cell is not 3 characters but 2 digits in cell "B4" and 5 digits in cell "B5" (6 characters if comma is included).
If the location of the character you want to extract moves from cell to cell, you can use the "FIND function" to solve this problem.
How to use the FIND function
The FIND function returns an integer that indicates how many characters of a particular string are present in a cell.
=FIND(search string,target,[start position])
Both full-width and half-width characters are counted as one character.
The "³" symbol is replaced by "A2To find out how many characters are in the "∗" column, enter the function as shown below.
=FIND("\", "}", "}", "}")A2)The number "7" was returned as shown in the figure below.
This is because the "³" mark is the seventh character counting from the left.
However, what we want to know is how many characters are to the right of the "³" mark.
To calculate it, use the LEN function.
How to use the LEN function
The LEN function is a function that can count the number of characters in a cell.
=LEN(string)
Both full-width and half-width characters are counted as one character.
."A2To count how many characters there are in the "+" cell, enter the following.
=LEN(A2)Then "10", the number of characters in the entire cell, is returned.
Combining the above results in the following "$20The number of characters to the right can be calculated from the "+" mark.
Number of characters in cell (10)" - "Number of characters up to ³ (7)" = "Number of characters in string to the right of ³ mark (3)".
This can be expressed as a function as in the following equation.
=LEN(A2)-FIND("\",A2)When this formula is reflected from "B2" cell to "B5" cell, the number of characters in the amount is reflected as shown in the figure below.
The following formula is the same as the first one when this function is reflected in the character count of the RIGHT function.
=RIGHT(A2,LEN(A2)-FIND("\",A2))When this formula is reflected from "B2" cell to "B5" cell, the amounts are reflected as shown in the figure below.
Conclusion
In this article, we explained how to extract the right side from a specific character, but if you want to extract the left side from a specific character, you can use theHow to extract characters before a specific character "LEFT and FIND functions" Excel (Excel)article for more information.
For detailed usage of the RIGHT function, seeExcel (Excel) RIGHT and LEFT functions to extract a specific number of characters from the right or left of a stringIt is also explained in
Comment