Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Extract all characters after specific string

Find the position of a specific character in the text

This tutorial describes the various ways we can extract all of the characters after the specific character in the text. In this tutorial in the first example, are used in the following Excel functions: FIND, SEARCH, LEN, RIGHT VALUE.

To get started, see the picture below. Column 'A' is an alphanumeric text. Our goal is to extract all the signs (characters) that are found after the character "C" (capital letter C). Also you notice that the column has a cell containing alphanumeric text in which the character "c" (lower-case c). This is important because if we can plan, which Excel function we need to use to return the position of the character "C".

search and find the position of a specific character in the text
In the picture above you see column 'C'. It is a set formula that returns a character position (character) the capital letter "C".


The formula in cell 'C2' is as follows: (copy the formula down to the last row). Also you notice the cells 'C14' and 'C16' error, which is returned to the formula. So Excel functions FIND differs small and large letter "C".

=FIND("C";A2;1)

The formula in cell 'D2' is as follows: (copy the formula down to the last row). Excel function SEARCH no differs case sensitive, and therefore for each cell in column 'D' back position "C"/"c"

=SEARCH("C";A2;1) this formula can be written in any other way =SEARCH("c";A2;1)

The formula in cell 'F2' is as follows: (copy the formula down to the last row). The formula returns or extracts all signs are after a specific character "C", which we have defined in the formula.

=RIGHT(A2;LEN(A2)-SEARCH("C";A2))

That formula above gives the result as text. To convert all the numbers in numeric format make Excel function VALUE.

=VALUE(RIGHT(A2;LEN(A2)-SEARCH("C";A2)))

Extracting of the string at the end of words in a cell that contains text and numbers

When we know the basic formula of how to calculate the number of the position of a specific character in the text, let's look at all that we can use the formula to display all the characters after the specific character of the text. See the image below. In the picture below you will see several columns that return the same result. If you look closely not all formula. Only one formula returns the correct result for all the cells in column 'A'. See all formulas below, all formulas extracted all the characters after specific character.

extract all signs from Excel cells containing alphanumeric text after specific the sign

In this tutorial in the second example, are used in the following Excel text functions: FIND, SEARCH, LEN, RIGHT, VALUE, SUBSTITUTE, MID, LEFT.

The formula in cell "B2" is as follows: (copy the formula down to the last row).

=RIGHT(A2;LEN(A2)-SEARCH("C";A2))

The formula in cell 'C2' is as follows: (copy the formula down to the last row).

=MID(SUBSTITUTE(A2;"C";"^";LEN(A2)-LEN(SUBSTITUTE(A2;"C";"")));FIND("^";SUBSTITUTE(A2;"C";"^";LEN(A2)-LEN(SUBSTITUTE(A2;"C";""))))+1;256)

The formula in cell 'D2' is as follows: (copy the formula down to the last row).

=RIGHT(A2;LEN(A2)-FIND("C";A2))

The formula in cell 'E2' is as follows: (copy the formula down to the last row).

=RIGHT(A2;LEN(A2)-FIND("*";SUBSTITUTE(A2;"C";"*";LEN(A2)-LEN(SUBSTITUTE(A2;"C";"")))))

The formula in cell 'F2' is as follows: (copy the formula down to the last row).

=MID(LEFT(A2; LEN(A2));FIND("^";SUBSTITUTE(A2;"C";"^";LEN(A2)-LEN(SUBSTITUTE(A2;"C";""))))+1;255