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".

When you have split into fields using your formula can't you then use a formula like the following on the values:

=IF(split_value<>"", split_value, NA())

Yes, I did that, but the na() values are not treated as missing values in the plot, even though that option (show empty cells as gaps) is selected.
Manually deleting these resulting NA fields, and the plot is correct.

Seems like only missing (empty) cells will plot as gaps, and formulae are able to produce blank or NA results, not empty.

I used this:

I can't replicate that problem.
Is it possible that you can upload the file?

Yes; attached.

As noted above, I think the main issue is that plotting only offers an option for "empty cells as gaps", and after splitting the available text data I have blank or NA cells which are not shown as gaps, and don't know how to have an expression return an empty cell. So all of the attempts below fail.

Basic idea;
Tab "Data" is raw data, downloaded form web site.
tab "Weather" then copies that (Cols A:C) except reversed order.
(to get dates forward order.)

Weather!C20:C234 is the copied data of interest.
It can be:
D nn (Direction, value)
D nn G nn (Direction, value, G, value)
Calm
then cols D:F are the split out values, which are plotted.

I left in a few experiments, trying to adjust the text values from the trim(....) expression.

One was to value() it (fails for blank), another to test for "" and NA() if found,
Another to add 0 to the text value to make it numeric (which fails if blank), then adding iferror() to adjust for those failures (but still needed to return something, and can't make an empty cell).

Attached Files

weatherData.xlsm (71.2 KB, 7 views)

I don't know if this is of any help, but I have no other idea.

Attached Files

weatherData_2.xlsm (67.5 KB, 8 views)

Thanks.

I think my analysis is correct, excel formulae cannot return empty (Hello MSoft, please add an empty() function!), so one has to use VBA.

I guess there is actually some logic to this, in that making the cell empty, would also erase the formula!

One solution is to have a second copied version of the data, but only copy if blank, and clear the copy destinations first (requires a macro or VBA). What a messy situation for a simple goal!

A better solution is for MSoft to allow for other missing values in graphs.

Maybe this can be of some help.

http://www.andypope.info/charts/brokenlines.htm

Wow - creative - but complex, and a marginal result!

I think I'll just make a macro to build a copy of the data, but not copying NA cells.