3

I have a google sheet with data that contains a column that is mixed both with text and numbers. I want to filter all to see which have numbers greater or equal to $500,000.

or which column have the format of '$ Volume: $xxx,xxx' that is >= $500,000.

Example of my data column D:

$xx $100.00 Jul 15 2022 Daily $ Volume: $132,707 Volume: 100

$xx $100.00 Jul 15 2022 Daily $ Volume: $500,000 Volume: 100

ADDED LINK TO SHEET: https://docs.google.com/spreadsheets/d/1PhQhkM6JxEJjHhiGIrWzMilwNwupAemrvcBVliRAB_I/edit?usp=sharing

Improve this question
3
  • Share a link to the spreadsheet (or to a copy of it) so that the volunteer contributors here have sufficient data, a place to test and the ability to know other important specs of the spreadsheet at a glance.
    – Erik Tyler
    Jun 20, 2022 at 19:45
  • Added as requested.
    – purple
    Jun 20, 2022 at 20:48
  • It seems by the fact that you've checked a reply as "Best Answer" that you've now got the solution(s) you were looking for.
    – Erik Tyler
    Jun 21, 2022 at 21:05

3 Answers 3

Reset to default
2

I replied over on reddit, but either of these will do it

=ARRAYFORMULA(
  FILTER(
   {A2:A,D2:D},
   --REGEXEXTRACT(D2:D,"Volume: (.*) Volume"))

or if you want to use your filter views, use this in another column

=ARRAYFORMULA(
  IF(ISBLANK(A2:A),,
   --REGEXEXTRACT(D2:D,"Volume: (.*) Volume")>=500000))
Improve this answer
1

As I didn't understand how you would like to highlight those rows which contain values> = 500000, I chose to highlight them with conditional formatting using this formula:

=value(regexextract(D2,"\$\w*:\$(\d*,\d*)"))>=500000

this is a link to a modified copy of your sheet

Improve this answer
2
  • Thank you for the suggestion. There seem to be some errors, some values over 500000 are not highlighted.
    – purple
    Jun 20, 2022 at 22:53
  • I saw ... the formula is wrong with values ​​greater than $ 999,999, basically when it contains 2 commas
    – Daniele
    Jun 21, 2022 at 2:07
1

To highlight matching rows in the sheet directly, use this conditional formatting custom formula rule for the range A2:E:

=regexmatch(Sheet1!$D2, "\$[5-9][\d,]{5,}")

To extract matching rows to a new tab, Insert > Sheet and put this filter() formula in cell A2:

=filter( Sheet1!A2:E, regexmatch(Sheet1!D2:D, "\$[5-9][\d,]{5,}") )

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.