How do I extract a value in a cell if it's followed by a specific text? EXCEL-CodePudding

How do I extract a value in a cell if it's followed by a specific text?

For example, Cell A2 contains "12 Count (Pack of 3) (Compatible with 2.0 Brewers)". I want to grab the value of 12 if it's followed by the word "count".

CodePudding user response：

If you do not have any word before your desired text then use-

=TEXTBEFORE(A1," Count")

If desired word situated in middle of sentence then could try-

=TAKE(TEXTSPLIT(TEXTBEFORE(A2," Count")," "),,-1)

CodePudding user response：

My approach is here in B3 cell,

=LEFT(CONCAT(IFERROR(--MID(A3,SEQUENCE(LEN(A3)),1),"")),2)

if your excel doesn't support for sequence, then try with in B4 cell,

=LEFT(CONCAT(IFERROR(--MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1),"")),2)