Excel tutorials

How to Extract All Characters After Specific String

Find the position of a specific character in the text

This Excel tutorial describes the various ways we can extract all of the characters after the specific character in the text. In this Excel 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 below: (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 Excel tutorial in the second Excel 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)

How to Display Text After a Specific Character

Below you can see the various variants of the solutions in the case where you need to separate the text or parts of a text after a particular character or a specific character.

Display text after a specific character

In the image above in the 'C2' cell has the formula below. This formula returns the last three characters of a text after a specific character (in this case it is a mathematical sign equal).

=RIGHT(A2;3)

In the 'C5, C6 and C7' cells is the formula below. This formula returns the entire text that is found after a specific character (in this case it is a mathematical sign equal). Note that a specific character is not always in the same position. Formula for Excel 2007 and higher.

=IFERROR(RIGHT(A5;LEN(A5)-FIND("=";A5));"")

In the 'C8, C9 and C10' cells is the formula below. This formula displays the entire text after a specific character (in this case it is a math sign equal). Note that a specific character is not always in the same position. Formula is for Excel 2003.

=IF(ISERROR(RIGHT(A8;(LEN(A8)-FIND("=";A8))));"";RIGHT(A8;(LEN(A8)-FIND("=";A8))))

In the cells 'C13, C14 and C15' is the formula below. This formula copies the text or string that is after the first specific character (in this case, it is a minus sign).

=TRIM(MID(SUBSTITUTE(A13;"-";REPT(" ";255));255;255))

In the cells 'C18, C19, C20 and C21' is the formula below. This formula returns a value or number that is after a specific character (in this case it is a minus sign). Data in 'A18 to A21' cells are formatted as text. Here it is important to note that this formula works only if a single character and two strings or an integer are formatted as text.

=--MID(A18;IFERROR(FIND("-";A18);0)+1;99)

Return All Text After the First Word or First Blank Sign

In the picture below you can see the data in column "A". The red font is marked with the first word or the first string that can be different lengths, ie. multiple characters. Our task is to copy or extract the full text of which is located after the first blank character (space character).

Extracting all text after the first distance ie. space character

The image above in cell 'C2' is the formula below (blue font). This formula copies the entire text contained after the first blank character ie. the spacing between words (space character) regardless of the number of characters in the first string.

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

The image above in the 'C8' cell is the formula below (the font of the green color). This formula extracts the entire text that is found after the first empty character, ie. spacing between the word (space character) regardless of the number of characters in the first string.

=RIGHT(A2;LEN(A2)-LEN(LEFT(A2;FIND(" ";A2;1))))

Notice in the formulas above that they are similar. The difference is in Excel nested functions used in two cases. These are SEARCH function and FIND function. These two Excel Functions differ significantly. See details on the relevant links.

Displaying the Parts of the Text by the Word Group Consisting of Sentence

In the picture below see another example, how can we by word groups copy the parts of the sentence to a certain length of the sentence being reduced.

Displaying parts of text by group of words from one cell

In the picture above, the 'D3' cell has the formula below. This formula uses the auxiliary condition from column "C". The row numbers in column "C" are a condition for copying text after a certain word that is in the position condition.

=MID($A$1;FIND(" ";$A$1;C3+1)+1;256)

In the image above, in cell 'A3' is the formula below. This formula is similar to the first formula but as a condition uses the Excel function ROW () which returns the number of rows as a result. So by copying down the ROW () function is variable and increases by +1.

=MID($A$1;FIND(" ";$A$1;ROW(A1)+1)+1;256)

Pay attention, you may need as a separator of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.