How to Extract Text After a Specific Character in Excel Using RIGHT and FIND

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.

A function to extract the text after the '$' symbol in cell A2

=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 "A2" cell.4In order to extract the amount of characters, the "string" must be "A2"cell, the "number of characters" is4characters, so enter the following

=RIGHT(A2,4)

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 four digits "6.00" of "156.00," as shown in the figure below.

This is because the amount in each cell is not 4 characters but 3 digits in cell "B4" and 6 digits in cell "B5".

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.

To find the position of the "$" sign in cell "A2", enter the function as shown below.

=FIND("$",A2)

The number "18" was returned as shown in the figure below.

This is because the "$" mark is the 18th 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.

To count the number of characters in cell A2, enter the following:

=LEN(A2)

This will return the total number of characters in the cell, which is 22.

Combining the above, you can calculate the number of characters to the right of the "$" sign as shown below.

Number of characters in cell (22)" - "Number of characters up to$ (18)" = "Number of characters in string to the right of$ mark (4)".

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