Forum Discussion

Copper Contributor

Nov 12, 2024

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

SergeiBaklan
Nov 12, 2024
As variant that could be

=TEXTBEFORE( TEXTAFTER(D$3, E5 & " $") & CHAR(10), "." & CHAR(10),,,1 )*1

for

and apply currency format to the result
Excel formula help (2).xlsx9 KB

SergeiBaklan
MVP
Nov 12, 2024
As variant that could be

=TEXTBEFORE( TEXTAFTER(D$3, E5 & " $") & CHAR(10), "." & CHAR(10),,,1 )*1

for

and apply currency format to the result
Excel formula help (2).xlsx9 KB
- vincenttys
  Copper Contributor
  Nov 17, 2024
  Thank you so much for your help. Greatly appreciated.
  - SergeiBaklan
    MVP
    Nov 20, 2024
    You are welcome
mathetes
Silver Contributor
Nov 12, 2024
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.
RevenueExtract.xlsx10 KB
- vincenttys
  Copper Contributor
  Nov 12, 2024
  Hi,
  They are all in one line.
  Anyway to solve it?
  - mathetes
    Silver Contributor
    Nov 12, 2024
    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?