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.
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.
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
Get FREE Advanced Excel Exercises with Solutions!
