Sometimes we have to copy data from other files into Excel. Most of the time, when we do this job, some special characters like commas, parentheses, etc. are trapped in the text. Excel contains amazing functions to extract text between two commas and other characters. In this article, we will only show how to extract text between two commas in Excel.
How to Extract Text Between Two Commas in Excel: 4 Methods
In our workbook, we have a list of 10 text strings, containing the name of a fruit, its amount, and delivery destination in the range of cells B5:B14. We will extract the destination of those states and keep them in the range of cells C5:C14.
Read More: How to Extract Text Before Character in Excel
1. SUBSTITUTE and MID Functions to Extract Text Between Two Commas
This process is an application of the SUBSTITUTE function. Besides that, we have to use MID and REPT functions to extract text between two commas. Our data list is in the range of cells B5:B14 and the result will be in the range of cells C5:C14. The steps of this procedure are given below:
📌 Steps:
- First of all, select cell C5 and write down the following formula for the cell.
=SUBSTITUTE(MID(SUBSTITUTE("," & B5&REPT(" ",6),",",REPT(",",255)),2*255,255),",","")
- Now, press the Enter key on your keyboard to get the result.
- After that, double-click on the Fill Handle icon with your mouse to copy the function up to cell C14. Or you can simply drag the Fill Handle box up to cell C14 to copy the formula.
Finally, we get the extracted text in our desired range of cells.
We are doing this breakdown for cell C5.
👉 REPT(“,”,255): This function actually returns “,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,”
👉 REPT(” “,6): This function returns “ “ or space
👉 SUBSTITUTE(“,” & B5&REPT(” “,6),”,”,REPT(“,”,255)): This function returns “,,,…,,,Orange-3000KG,,,…,,, Bronx,,,…,,,, NewYork”
👉 MID(SUBSTITUTE(“,” & B5&REPT(” “,6),”,”,REPT(“,”,255)),2*255,255) This function returns “,,,Bronx,,,”
👉 SUBSTITUTE(MID(SUBSTITUTE(“,” & B5&REPT(” “,6),”,”,REPT(“,”,255)),2*255,255),”,”,””) This function returns “Bronx”.
Read More: How to Extract Text after a Specific Text in Excel
2. Applying MID and SEARCH Functions to Extract Text
In this method, we will use the SEARCH and MID functions to extract the text between two commas. We are using the same data list which we have used in the previous process. The procedure is explained below:
📌 Steps:
- First, select Cell C5.
- Then, Write down the following formula in cell C5.
=MID(B5,SEARCH(",",B5)+1,SEARCH(",",B5,SEARCH(",",B5)+1)-SEARCH(",",B5)-1)- Press the Enter button.
- Now, double-click on the small Fill Handle icon in the left bottom corner of the cell to copy the formula up to Cell C14.
- You will get the extracted text in the range of cell C5:C14.
Thus, we can say that our formula has worked perfectly.
🔍 Breakdown of the Formula
We are doing this breakdown for Cell C5.
👉 SEARCH(“,”,B5): This function returns 14.
👉 SEARCH(“,”,B5) – 1: This function returns 13.
👉 SEARCH(“,”,B5) + 1: This function returns 15.
👉 SEARCH(“,”,B5,SEARCH(“,”,B5)+1): This function returns 21.
👉 SEARCH(“,”,B5,SEARCH(“,”,B5)+1) – SEARCH(“,”,B5) – 1: This function returns 6.
👉 MID(B5, SEARCH(“,”,B5)+1,SEARCH(“,”,B5,SEARCH(“,”,B5)+1)-SEARCH(“,”,B5)-1) : This function returns Bronx.
Read More: How to Extract Text After First Space in Excel
3. Using TRIM, MID and SUBSTITUTE Functions
In this case, we will use the TRIM function to extract the test between two commas. Besides that, we have to use MID, SUBSTITUTE, and REPT functions to extract text between two commas. This procedure is almost similar to the others, however, we have to write a new formula to get the result. The steps are described below:
📌 Steps:
- First of all, select cell C5.
- Now, write down the following formula in cell C5.
=TRIM(MID(SUBSTITUTE(B5,",",REPT(" ",100)),100,100))
- Then, click on the small Fill Handle icon on the left bottom corner of the cell twice to copy the formula up to cell C14. Or drag the box up to cell C14.
- At last, the text between two commas will be in the range of cells C5:C14.
So, we can see that the formula worked accurately.
We are doing this breakdown for Cell C5.
👉 REPT(” “,100): This function returns “ “ or space.
👉 SUBSTITUTE(B5,”,”,REPT(” “,100)): This function returns ” Orange-3000KG … Bronx … NewYork “.
👉 MID(SUBSTITUTE(B5,”,”,REPT(” “,100)),100,100): This function returns “ Bronx ”. Actually, it returns a large space before and after Bronx.
👉 TRIM(MID(SUBSTITUTE(B5,”,”,REPT(” “,100)),100,100)): This function returns “Bronx”.
Similar Readings
4. Text to Column Option to Extract Text Between Two Commas
Through this process, we can completely separate the strings at the position of any special character. In this case, we don’t have to use any formula like the previous methods. The dataset contains two commas and those commas are between two texts. So, after doing this process the string will separate into three different columns. The steps are given below as follows:
📌 Steps:
- Select the entire data list in the range of cells B5:B14.
- Now, go to the Data tab and select the Data Tools > Text to Columns.
- A dialog box, entitled Convert Text to Columns Wizard, will appear.
- Then, choose the Delimited option and click the Next button.
- After that, choose the Delimiters as Comma and click the Next button.
- Keep the data type in General and change the Destination of the data from $B$5 to $C$5.
- Now, click Finish to complete the process.
- As we made the heading before completing the process, Excel may give you a warning like the image shown below.
- Ignore the warning and click OK.
- Finally, you will get the extracted data into the range of cells C5:E14.
Using this process, we can extract all the data of a string and store them in three separate columns.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you may be able to extract text between two commas in an Excel worksheet. If you have any further queries or recommendations, please share them with us in the comments section below.
Related Readings
- How to Extract Text after Second Comma in Excel
- How to Extract Text between Two Spaces in Excel
- How to Extract Text Between Two Characters in Excel
- How to Extract Certain Text from a Cell in Excel VBA
- How to Extract Text After a Character in Excel
<< Go Back to Extract Text in Excel | String Manipulation | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
