Google-sheets – How to split multiple values in one cell into separate cells

google sheetsimporthtml

I'm using ImportHTML to pull in data scraped using another tool called Import.io, as you can see in the example spreadsheet here. However, when I pull in data using ImportHTML, it's pulling three years of data into one cell, so one cell contains the column heading and three years of financial data.

I want to break out the three numbers in each cell so that there is only one number per cell, but can't seem to figure it out. I've tried splitting the data, trimming it then splitting it, converting using =Value(), but can't get the data into its own cell.

Best Answer

Line breaks inside a Google Sheets cell are encoded as CHAR(10). (The function CHAR returns the character with specified numeric code, and 10 is used to represent new line control character.) So, you should use

=SPLIT(D3,CHAR(10))

Method 1: match

Use custom formula

=iserror(match(B2, Sheet1!$A$1:$A$6, 0))

for data validation.

B2 is the upper left corner of the range to which the rule is applied.
The third parameter 0 requires exact match
The formula returns True only if match returns an error, which means no match is found.

Data validation accepts the values for which the formula evaluates to True. Same can be done with conditional formatting.

Method 2: regexmatch

Form a regular expression from the list and validate against that. The string

 "^(" & join("|", filter(Sheet1!$A1:$A, len(Sheet1!$A1:$A))) & ")$"

concatenates all values in that column into a regular expression that will match any of them. (For example, "^(first|second|third)$").

Then you can use regexmatch by this string to validate or conditionally format the data anywhere else. For example:

=not(regexmatch(B2, "^("&join("|", filter(Sheet1!$A1:$A, len(Sheet1!$A1:$A)))&")$"))

which means the content must not match the regular expression.

Best Answer

Related Solutions

Google-sheets – Retrieve last entry via ImportXML

Google-sheets – Highlight/reject cells that contain any of the values from another sheet

Method 1: match

Method 2: regexmatch

Related Topic