Forum Discussion
Excel formula help
Hi all,
I have a cell where it contains information such as below in cell A1. I want to use a formula to extract the dollar value of Orange (Navel) which is $1,091.49. This figure will change week on week range from $0 to $100,000,000. Any idea how can I do that?
Revenue Apple $6,854,191.79.
Revenue Orange (Navel) $1,091.49.
Revenue Orange (Blood) $7,459.50.
Revenue Water Melon $344.16.
Revenue Pineapple $43,540.05.
Thank you
Regards,
Vince
As variant that could be
=TEXTBEFORE( TEXTAFTER(D$3, E5 & " $") & CHAR(10), "." & CHAR(10),,,1 )*1for
and apply currency format to the result
10 Replies
As variant that could be
=TEXTBEFORE( TEXTAFTER(D$3, E5 & " $") & CHAR(10), "." & CHAR(10),,,1 )*1for
and apply currency format to the result
Thank you so much for your help. Greatly appreciated.
You are welcome
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)))
