-3

I have a source .csv file which is non-editable, I'd like to extract the quantity that is enclosed within a particular text. I've tried using MID with a combination of LEFT and RIGHT. However, I've only been able to get either of them (before / after).

I'd like to extract the text after QTY : and anything before )

Example:

(Empty : Empty - Colour : Mixed colours - QTY : 22)

The text I'm interested in is - "22"

Navik Goswami
  • 107
  • 8

2 Answers2

2

You could use combination of mid and find functions

=MID(A1,FIND("QTY :",A1)+LEN("QTY :"),(FIND(")",A1) - (FIND("QTY :",A1)+LEN("QTY :"))))

still_learning
  • 36
  • 3
1

You can take advantage of this formula:

=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)

Credits go here.

zipa
  • 27,316
  • 6
  • 40
  • 58