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

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. trap 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.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


4 Methods to Extract Text Between Two Commas in Excel

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.


1. SUBSTITUTE and MID Functions to Extract Text Between Two Commas

This process is an application of the SUBSTITUTE function. Besides it, we have to use MID and REPT functions also 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 into the cell.
=SUBSTITUTE(MID(SUBSTITUTE("," & B5&REPT(" ",6),",",REPT(",",255)),2*255,255),",","")

Use of SUBSTITUTE Function to Extract Text Between Two Commas

  • Now, press the Enter key on your keyboard to get the result.

Use of SUBSTITUTE Function to Extract Text Between Two Commas

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

Use of SUBSTITUTE Function to Extract Text Between Two Commas

Finally, we get the extracted text in our desired range of cells.

πŸ” Breakdown of the Formula

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 Between Two Characters in Excel (4 Methods)


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 into Cell C5.
=MID(B5,SEARCH(",",B5)+1,SEARCH(",",B5,SEARCH(",",B5)+1)-SEARCH(",",B5)-1)
  • Press the Enter button.

Applying MID and SEARCH Functions to Extract Text

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

Applying MID and SEARCH Functions to Extract Text

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 Second Comma in Excel (6 Methods)


Similar Readings


3. Using TRIM, MID and SUBSTITUTE Functions

In this case, we will use the TRIM function to extract the test between two commas. Besides it, we have to use MID,Β SUBSTITUTE, andΒ  REPT functions also 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))

Using TRIM, MID and SUBSTITUTE Functions

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

Using TRIM, MID and SUBSTITUTE Functions

  • At last, the text between two commas will be in the range of cells C5:C14.

Using TRIM, MID and SUBSTITUTE Functions

So, we can see that the formula worked accurately.

πŸ” Breakdown of the Formula

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”.

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


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.

Text to Column Option to Extract Text Between Two Commas

  • A dialog box, entitled Convert Text to Columns Wizard, will appear.
  • Then, choose the Delimited option and click the Next button.

Text to Column Option to Extract Text Between Two Commas

  • After that, choose the Delimiters as Comma and click the NextΒ button.

Text to Column Option to Extract Text Between Two Commas

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

Text to Column Option to Extract Text Between Two Commas

  • As we made the heading of our before completing the process, Excel may give you a warning like an image shown below.
  • Ignore the warning and click OK.

  • Finally, you will get the extract data into the range of cells C5:E14.

Text to Column Option to Extract Text Between Two Commas

Using this process, we can extract all the data of a string and can store them in three separate columns.

Read More: How to Extract Text After First Space in Excel (3 Methods)


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.

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


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo