How to Extract Data between Minus

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


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


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)

Step 4: To delete all the extra minus

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

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