**Method 1 – SUBSTITUTE and MID Functions to Extract Text Between Two Commas**

** Steps:**

- Select cell
**C5**and write down the following formula for the cell.

`=SUBSTITUTE(MID(SUBSTITUTE("," & B5&REPT(" ",6),",",REPT(",",255)),2*255,255),",","")`

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

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

**Method 2 – Applying MID and SEARCH Functions to Extract Text**

** Steps:**

- Select
**Cell C5**.

- Write down the following formula in cell
**C5.**

`=MID(B5,SEARCH(",",B5)+1,SEARCH(",",B5,SEARCH(",",B5)+1)-SEARCH(",",B5)-1)`

- Press
**Enter**.

- Double-click on the small
**Fill Handle**icon in the bottom left corner of the cell to copy the formula up to**Cell C14.**

- Get the extracted text in the range of cell
**C5:C14.**

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

**Method 3 – Using TRIM, MID and SUBSTITUTE Functions**

** Steps:**

- Select cell
**C5**.

- Write down the following formula in cell
**C5**.

`=TRIM(MID(SUBSTITUTE(B5,",",REPT(" ",100)),100,100))`

- Click on the small
**Fill Handle**icon the left bottom corner of the cell twice to copy the formula up to cell**C14.**Or drag the box up to cell**C14.**

- The text between two commas will be in the range of cells
**C5:C14**.

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

**Method 4 – Text to Column Option to Extract Text Between Two Commas **

** Steps:**

- Select the entire data list in the range of cells
**B5:B14.** - Go to the
**Data**tab and select the**Data Tools > Text to Columns**.

- A dialog box, entitled
**Convert Text to Columns Wizard**, will appear. - Choose the
**Delimited**option and click the**Next**button.

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

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

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