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.

**Table of Contents**hide

## 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),",","")`

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

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

**Similar Readings**

**Extract Text Before Character in Excel (4 Quick Ways)****How to Extract Text After Last Space in Excel (5 Ways)****Extract Text After a Character in Excel (6 Ways)**

**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))`

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

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

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

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!