How to split multiple values in one cell into separate cells?

Question

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.

Answer 1

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))