Excel: extract text between two different characters

root's picture

Today I had to extract in excel a value from a text which is always the same. Only the value is different.
Text in the cell is actually a rear backup output showing backup time: "Archived X MiB in X seconds [avg X KiB/sec]"

Ok, let's give an example.

Extract 289 from "Archived 2900 MiB in 289 seconds [avg 10275 KiB/sec]"

This text is in cell A2 and the result will be in B2 (we will put the formula below there).
The needed formula is: =MID(A2,SEARCH("in",A2)+2,SEARCH("seconds",A2)-SEARCH("in",A2)-2)

That's about it but what if A2 does not have the text added yet? You will have errors all over the place.
No worries, include all in =IFEROR(), "") which means If there is an error, show nothing:
=IFERROR(MID(A2,SEARCH("in",A2)+2,SEARCH("seconds",A2)-SEARCH("in",A2)-2), "")

Thou shalt not steal!

If you want to use this information on your own website, please remember: by doing copy/paste entirely it is always stealing and you should be ashamed of yourself! Have at least the decency to create your own text and comments and run the commands on your own servers and provide your output, not what I did!

Or at least link back to this website.

Recent content

root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root
root