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.

**Table of Contents**hide

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

**🔍 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 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.

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

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