Thread: text-to-Fields failing - how get null for missing values?
View Single Post
 
Old 06-08-2016, 08:48 PM
guthrie guthrie is offline Windows 7 64bit Office 2010 64bit
Novice
  
Join Date: Mar 2011
Posts: 10
guthrie is on a distinguished road
Default test fot null? use 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:

Quote:
=IF(
TRIM(MID(SUBSTITUTE($C25," ",REPT(" ",LEN($C25))),((COLUMNS($C25:F25)-1)*LEN($C25))+1,LEN($C25)))="",
NA(),
VALUE(TRIM(MID(SUBSTITUTE($C25," ",REPT(" ",LEN($C25))),((COLUMNS($C25:F25)-1)*LEN($C25))+1,LEN($C25)))))
Reply With Quote