How to Extract Data between Minus

To extract data between minus, you need to first find the location of minus, then apply Mid Function.

Formula:

Copy the formula below and replace "A2" with the first cell that contains the text you would like to extract, then copy down.

=SUBSTITUTE(MID(SUBSTITUTE("-" & A2& REPT(" ",10),"-", REPT("-",300)), 2*300,300),"-","")

Explanations:

Extract the data between minus from "How to Extract-Data-between Minus".

Step 1: Put minus at the beginning of the data

Formula="-" & A2&REPT(" ",10)
Result-How to Extract-Data-between Minus

Step 2: To have more minus to separate the data between

Formula=SUBSTITUTE("-" & A2&REPT(" ",10),"-",REPT("-",300))
Result------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------How to Extract------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Data------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------between Minus

Step 3: To extract the middle part

Formula=MID(SUBSTITUTE("-" & A2&REPT(" ",10),"-",REPT("-",300)),2*300,300)
Result---------------Data--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Step 4: To delete all the extra minus

Formula=SUBSTITUTE(MID(SUBSTITUTE("-" & A2&REPT(" ",10),"-",REPT("-",300)),2*300,300),"-","")
ResultData

Step 5: In case there are extra spaces before or after the extracted data, use Trim function to remove them.

Download Example

Leave a Reply