How to Extract Text after Second Comma in Excel?

Method 1 – Extract Text after the Second Comma with MID and FIND Functions

We have a dataset containing Teams and Top players. Our main goal is to extract text after the second comma. We’ll extract text from column C in column D.

Steps:

Use the following formula in cell D5:

=MID(C5,FIND(",",C5, FIND(",",C5)+1)+1,256)

Press Enter and drag the Fill handle icon.

Here’s the result.

How Does the Formula Work?

FIND(“,”,C5, FIND(“,”,C5)+1)+1 finds the second comma in cell C5 and returns that position.
The MID(C5, FIND(“,”,C5, FIND(“,”,C5)+1)+1,256) will parse out the text string after the second comma. The first argument specifies the text string in cell C5. The second argument indicates the position of the second comma, followed by the third argument indicating the total number of characters.

Method 2 – Using TRIM, MID, and SUBSTITUTE Functions in Excel

We’ll extract text in column D.

Steps:

Use the following formula in the cell D5:

=TRIM(MID(C5,FIND("#",SUBSTITUTE(C5,",","#",2))+1,255))

Press Enter and drag the Fill handle icon.

You will be able to extract text after second comma like the following.

How Does the Formula Work?

SUBSTITUTE(C5,”,”,”#”,2) finds out and replaces the second comma with the ‘#‘ Character in cell C5.
FIND(“#”,SUBSTITUTE(C5,”,”,”#”,2))+1 provides the position of the ‘#‘ character.
The MID function provides the characters of the text string and the TRIM function removes the extra spaces.

Method 3 – Combining RIGHT with LEN and SEARCH Functions

We’ll extract text in column D.

Steps:

Use the following combined formula in the cell D5:

=RIGHT(C5,LEN(C5)-(SEARCH(",",C5,SEARCH(",",C5)+1)))

Press Enter and drag the Fill handle icon.

Here’s the result.

How Does the Formula Work?

SEARCH(“,”, C5, SEARCH(“,”, C5) + 1 provides the number of characters at which the second comma is in cell C5.
LEN(C5) – (SEARCH(“,”, C5, SEARCH(“,”, C5) + 1 subtracts the number of characters from the text string corresponding to the position of the second comma.
The RIGHT function will provide the text after the second comma.

Method 4 – Combination of INDEX and FILTERXML Functions to Extract Text

We’ll extract text in column D.

Steps:

Use the following combined formula in the cell D5:

=INDEX(FILTERXML("<a><b>"&SUBSTITUTE(C5,",","</b><b>")&"</b></a>","//b"),3)

Press Enter and drag the Fill handle icon.

Here’s the result.

How Does the Formula Work?

SUBSTITUTE(C5,”,”,”*/b>*b>”)&”*/b>*/a>”,”//b”) function determines the second comma in the cell C5 and replaces it with the special character.
The FILTERXML function divides the text string of cell C5 into delimiters and returns the specific value after the second comma.
The INDEX function will provide the text after the second comma.

Method 5 – Extract Text after the Second Comma Using IFERROR and MID Functions

We’ll extract text to column D.

Steps:

Use the following combined formula in cell D5:

=IFERROR(MID(C5,FIND(",",C5,FIND(",",C5)+1)+1,256),"")

Press Enter and drag the Fill handle icon.

Here’s the result.

How Does the Formula Work?

FIND(“,”,C5, FIND(“,”,C5)+1)+1 finds out the second comma in cell C5 and returns that position.
The MID(C5, FIND(“,”,C5, FIND(“,”,C5)+1)+1,256) will parse out the text string after the second comma. As you can see, this first argument specifies the text string in cell C5. Secondly, the second argument indicates the position of the second comma, followed by the third argument indicating the total number of characters. The IFERROR function will return a value error if the expression is an error in the value of the expression itself otherwise.

Similar Readings