I have a table of imported data (from an “external data source” URL web page).
The original data fields (wind reports) look like this: S 16 G 20

Then I extract (copy) a few columns onto another sheet, with entries that look like:
=data!C216 (pointing to the column of imported data).
etc. down the column.

Then I sort those (by another column (dates)), preparing for a graph.

Then I want to separate out the two numeric entries for graphing.
My first try was using the icon for the built-in function for this, but when I run a "text-to-columns" on it, it considers the reference equation as the text, not the data that it points to.

So for example, text-to-columns of this gives me a single entry (treating the formula as a string):
| S 16 G 20 |
as it copies the formula itself and thus shows the same (original) data, when I want it to operate on the string the formula points to:
| S | 16 | G | 20 |

In any case, I prefer to split into fields using an equation, so I don’t have to automate that operation.

So instead using a splitting formula;
=TRIM(MID(SUBSTITUTE($C36," ",REPT(" ",LEN($C36))),((COLUMNS($C36:E36)-1)*LEN($C36))+1,LEN($C36)))

works fine, but for empty entries (from short fields) it puts text “” into a cell, which graphs as zero, instead of a missing data point. Converting to value by test for that, and instead put na() in the cell, instead of zero shows as connected lines on the graph, even though “show empty cells as gaps” is selected (and works, if I delete these cells they are gaps).

PS: This whole thing works fine if I just use the text-to-columns button on the original text field(!).

So guess it comes to, how do I get the splits to be empty (blank) cells for short entries, instead of (“”, or #NA)?



So I guess the simple version of the question is:
"how to split into fields, have known numerics as numbers for plotting, and have missing values be empty".