Extract date and numbers in google sheet from steam price history
I have this link with a lot of info: https://steamcommunity.com/market/pricehistory/?currency=1&appid=570&market_hash_name=Exalted%20Fractal%20Horns%20of%20Inner%20Abysm
I want to extract all the data of each date and put in a list divided in date, median price and quantity
I am using IMPORTDATA Function but doesn't support all the lenght of the data.
So I take it manually in a sheet:
Then I want to divide this in a list as:
| Date | Median Price | Quantity |
|---|---|---|
| Nov 05 2022 23 | 193.82 | 4 |
I think it can be done with REGEXEXTRACT but I don't know what regular_expression to use.
Already figure out the date median price formula:
=ARRAYFORMULA(REGEXEXTRACT(FLATTEN(SPLIT(A2,"[")),"(.{3} \d+ \d{4}.{3}).+,(.+),"""))
Need and adjustment for show the quantity.
Solution
try:
=INDEX(QUERY(QUERY(REGEXEXTRACT(FLATTEN(SPLIT(A2,
"[")), "(.{3} \d+ \d{4}).+,(.+),""")*1,
"select Col1,avg(Col2),count(Col1) group by Col1"), "offset 1", ))
UPDATE:
=INDEX(REGEXEXTRACT(FLATTEN(SPLIT(A2,
"[")), "(.{3} \d+ \d{4}).+,(.+),""(\d+)"))
- Adding leading zero with regular expression
- RegEx for Javascript to allow only alphanumeric
- How can I validate an email address in JavaScript?
- how to extract floating numbers from strings in javascript
- .NET regex: add space before all & characters
- Grep: Is there a special way to include "no character" as a permutation for a "character position" match?
- Php mb_ereg_match faulty match
- Regular Expression (RegEx) for IPv6 Separate from IPv4
- print the matched word in perl regex
- Regex help, works on rubular not on production? Possible \n issue?
- Match all files in directory and index files from first level children
- What is a reliable isnumeric() function for python 3?
- Find all elements whose id begins with a common string
- Capture multiple instances in same line of complex expression
- How to create a regex that will match an arbitrary sequence of spaces and tabs
- RegEx that matches positive numbers
- Regex: ignore case sensitivity
- PHP: using REGEX to get the tablename from a mysql query
- Javascript email validation regex inclusion
- RE2 or Go Regex to match all ccTLD emails except for .us or .co
- Detect if a string contains uppercase characters
- Regex lookahead, lookbehind and atomic groups
- regex - what is the pattern for floating point digit next to unit
- JavaScript Regex to match a URL in a field of text
- preg_replace second parameter ($replacement) doesnt fit
- How to string manipulate/extract subject contents in a CSR using OpenSSL command + Perl?
- Why regex searching second group from the end?
- Different spellings of Chanukah Regex
- Rails / Ruby method / regex to increment numeric part of string?
- Nginx server_name - Site Regular Expression - Matching Multiple sites