Saquib Ahmad Shuvo
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.
How to Extract Text after Second Comma in Excel: 6 Methods
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 the Second Comma with MID and FIND Functions
Here, we have a dataset containing Teams 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.
📌 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.
- Finally, you will be able to extract text after the 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: How to Extract Text Before Character in Excel
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.
📌 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.
- Finally, you will be able to extract text after second comma like the following.
🔎 How Does the Formula Work?
- Here, SUBSTITUTE(C5,”,”,”#”,2) finds 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.
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.
- Finally, you will be able to extract text after the second comma like the following.
🔎 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 after a Specific Text in Excel
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.
📌 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.
- Finally, you will be able to extract text after the 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 After First Space in Excel
5. Extract Text after the 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.
📌 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.
- Finally, you will be able to extract text after the 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 in the value of the expression itself otherwise.
Similar Readings
- How to Extract Text after Second Space in Excel
- How to Extract Text After Last Space in Excel
- How to Extract Text Between Two Commas in Excel
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.
📌 Steps:
- Firstly, go to the Insert tab and select the Table option to convert the dataset into a table.
- 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.
- Next, go to the Data tab and select From Table/Range.
- You will now see the Power Query window as shown below.
- Select the column from which you would like to extract texts.
- 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.
- Finally, you will be able to extract text after the second comma like the following.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
