IMPORTFROMWEB always tries to convert dates, numbers and currencies into a format that is readable by your spreadsheet.
However your extracted data might not always be ready to be manipulated. For that purpose we offer advanced users the ability to clean data straight into the function thanks to regular expressions.
What are Regular Expressions
A regular expression (regex or regexp for short) is a special text string for describing a search pattern. You can think of regular expressions as wildcards on steroids.
Regular expressions can be added as 3rd and 4th parameters of the function, before the options
=IMPORTFROMWEB(data_sources, selectors, regexps, replaceBy, options)
Check this article for the most useful regex rules to convert numbers, dates or currencies:
Useful regex rules to clean up your imported data
The regexps parameter
(optional) One or a list of regular expressions to extract or replace specific parts from the content returned by each selector.
- If the next argument replaceBy is not filled or NULL, it will extract some text from the content.
- If replaceBy has a list of values, they will be used to replace the extracted content.
Each regexp from the list is associated to one selector from the list of selectors in such way that the first regexp will extract content from the results of the first selector, etc.
It works the same way as the JavaScript String match() function
Note that, because they’re optional and mostly reserved to advanced users, regexps don’t appear in the tooltip. regexps should always be the 3rd argument.
The replaceBy parameter
(optional) One or a list of texts to replace the content extracted by the regexps.
Each string of the list is associated to one regexp from the list of regexps in such way that the first string will replace the content extracted by the first regexp, etc.
Note that, because it’s optional and mostly reserved to advanced users, replaceBy don’t appear in the tooltip. replaceBy should always be the 4th argument.