Forum Discussion
Excel formula help
As variant that could be
=TEXTBEFORE( TEXTAFTER(D$3, E5 & " $") & CHAR(10), "." & CHAR(10),,,1 )*1for
and apply currency format to the result
Try this formula in an adjacent column. And copy down. (I am assuming that it's not all of those lines in cell A1; rather that they appear in A1:A5.
=LET(lngth,LEN(A1),start,FIND("$",A1),VALUE(MID(A1,start,lngth-start)))See attached.
Hi,
They are all in one line.
Anyway to solve it?
This is not the most elegant solution, I'm sure. Somebody else can possibly supply that. But if the different headers on each line remain constant, with only the values changing, it's possible to pull out the value of the numbers after "Revenue Orange (Navel)" and preceding "Revenue Orange (Blood)" by this formula
=LET(start,FIND("Navel",A1)+8,lngth,FIND("Revenue Orange (Bl",A1)-(start+2),VALUE(MID(A1,start,lngth)))Thanks for taking time looking into this.
Unfortunately, the header changes the position week on week.
Unfortunately, the header changes the position week on week.
Also unfortunate: your original post only mentioned the amount as variable week to week. My admittedly less-than-elegant suggestion handled that, but not all kinds of change.
I hope that the solution offered by SergeiBaklan works; he's far and away one of the best here.
Point of clarification: Do you just (only) want "to extract the dollar value of Orange (Navel) which is $1,091.49"? i.e., not the other values?
That is correct. Also, the position of Orange (Navel) may change from week to week. I am guessing i need to incorporate Search or Find function?