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),"-","") |
|---|---|
| Result | Data |
Step 5: In case there are extra spaces before or after the extracted data, use Trim function to remove them.
