How to Extract Text after Second Comma in Excel (6 Methods)

If you are looking for some special tricks to extract text after the second comma in Excel, you’ve come to the right place. In Microsoft Excel, there are numerous ways to extract text after the second comma. In this article, we’ll discuss six methods to extract text after the second comma. Let’s follow the complete guide to learn all of this.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


6 Methods to Extract Text after Second Comma in Excel

We will use six effective and tricky methods to extract text after the second comma in Excel in the following section. This section provides extensive details on six methods. You should learn and apply all of these, as they improve your thinking capability and Excel knowledge.


1. Extract Text after Second Comma with MID and FIND Functions

Here, we have a dataset containing Team and Top players. Our main goal is to extract text after the second comma. To extract text, we will combine MID and FIND functions. Here, the Mid function returns the character from the middle of a text string. The FIND function returns the starting position of one text string within another text string. You have to follow the following steps to extract text in column D.

Extract Text after Second Comma with MID and FIND Functions

πŸ“Œ Steps:

  • We will use the following formula in the cell D5:

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

  • Press Enter and drag the Fill handle icon.

Extract Text after Second Comma with MID and FIND Functions

  • Finally, you will be able to extract text after second comma like the following.

πŸ”Ž How Does the Formula Work?

  • Here, FIND(β€œ,”,C5, FIND(β€œ,”,C5)+1)+1 finds out the second comma in cell C5 and returns that position..
  • Finally, 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.

Read More: Extract Text After a Character in Excel (6 Ways)


2. Using TRIM, MID, and SUBSTITUTE Functions in Excel

To extract text, we will combine TRIM, MID, SUBSTITUTE, and FIND functions. Here, the TRIM function removes all spaces from a text string and the SUBSTITUTE function replaces existing text with new text in a text string. You have to follow the following steps to extract text in column D.

How to Extract Text after Second Comma in Excel

πŸ“Œ Steps:

  • We will 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.

How to Extract Text after Second Comma in Excel

  • Finally, you will be able to extract text after second comma like the following.

How to Extract Text after Second Comma in Excel

πŸ”Ž How Does the Formula Work?

  • Here, SUBSTITUTE(C5,”,”,”#”,2) find out and replaces the second comma with the β€˜#β€˜ Character in cell C5.
  • Next, FIND(β€œ#”,SUBSTITUTE(C5,”,”,”#”,2))+1 provides the position of the β€˜#β€˜ character.
  • Finally, the MID function provides the characters of the text string and the TRIM function removes the extra spaces.

Read More: Extract Text Before Character in Excel (4 Quick Ways)


3. Combining RIGHT with LEN and SEARCH Functions

To extract text, we will combine RIGHT, LEN, and SEARCH functions. Here, the RIGHT function returns the specified number of characters from the end of a text string. The LEN function returns the number of characters in a text string. The SEARCH function returns the number of characters at which a specific character or text string is first found. You have to follow the following steps to extract text in column D.

πŸ“Œ Steps:

  • We will 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.

How to Extract Text after Second Comma in Excel

  • Finally, you will be able to extract text after second comma like the following.

How to Extract Text after Second Comma in Excel

πŸ”Ž How Does the Formula Work?

  • Here, SEARCH(β€œ,”, C5, SEARCH(β€œ,”, C5) + 1 provides the number of characters at which the second comma is in cell C5.
  • Next, LEN(C5) – (SEARCH(β€œ,”, C5, SEARCH(β€œ,”, C5) + 1 subtracts the number of characters from the text string corresponding to the position of the second comma.
  • Finally, the RIGHT function will provide the text after the second comma.

Read More: How to Extract Text Between Two Commas in Excel (4 Easy Approaches)


Similar Readings


4. Combination of INDEX and FILTERXML Functions to Extract Text

To extract text, we will combine INDEX, FILTERXML, and SUBSTITUTE functions. Here, the INDEX function returns a value or reference of the cell at the intersection of a particular row and column in a given range. The FILTRXML function returns specified data from the XML content by using the specified path. You have to follow the following steps to extract text in column D.

Combination of INDEX and FILTERXML Functions to Extract Text

πŸ“Œ Steps:

  • We will 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.

Combination of INDEX and FILTERXML Functions to Extract Text

  • Finally, you will be able to extract text after second comma like the following.

πŸ”Ž How Does the Formula Work?

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

Read More: How to Extract Text from a Cell in Excel (5 ways)


5. Extract Text after Second Comma Using IFERROR and MID Functions

To extract text, we will combine IFERROR, MID, and FIND functions. You have to follow the following steps to extract text in column D.

Extract Text after Second Comma Using IFERROR and MID Functions

πŸ“Œ Steps:

  • We will use the following combined formula in the cell D5:

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

  • Press Enter and drag the Fill handle icon.

Extract Text after Second Comma Using IFERROR and MID Functions

  • Finally, you will be able to extract text after second comma like the following.

πŸ”Ž How Does the Formula Work?

  • Here, FIND(β€œ,”,C5, FIND(β€œ,”,C5)+1)+1Β finds out the second comma in cell C5 and returns that position.
  • Finally, 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 the value of the expression itself otherwise.

6. Utilizing Power Query in Excel

Here, we will discuss another method to extract text after the second comma by using the Power Query of Excel. You have to follow the following steps to extract text.

Utilizing Power Query in Excel

πŸ“Œ Steps:

  • Firstly, go to the Insert tab and select the Table option to convert the dataset into a table.

Utilizing Power Query in Excel

  • When the Create Table dialog box appears, select the range of data and click on OK.

  • Then, you will be able to convert the dataset into a table like the following.

Utilizing Power Query in Excel

  • Next, go to the Data tab and select From Table/Range.

How to Extract Text after Second Comma in Excel

  • You will now see the Power Query window as shown below.

  • Select the column from which you would like to extract texts.

How to Extract Text after Second Comma in Excel

  • Select Transform and select Extract. Then, select Text After Delimiter.

  • When the Text After Delimiter dialog box appears, enter a comma in the Delimiter option and type 1 in the Number of delimiters to skip. Click OK.

  • You will get the output like the following.

  • Now, go to the Home tab and click on the Close & Load icon.

How to Extract Text after Second Comma in Excel

  • Finally, you will be able to extract text after the second comma like the following.


Conclusion

That’s the end of today’s session. I strongly believe that from now you extract text after the second comma in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo