How to make readtable always return NaN when it detects a blank cell

My matlab code always generate NaN for blank cells. When I run it now I sometimes get NaN and sometimes I get {0×0 char}. I am reading in an excel file that for both examples attached is generated by the same R-code.

clear all;
close all;

%Test 1 Does work
fn        = 'IMPORT_CONTROL.xlsx';        
tBC       = [];
opt       = detectImportOptions(fn);
shts      = sheetnames(fn);
new_details = cell(length(shts),2);

new_details{1,1} = [shts(1)];
readtable(fn,opt,'Sheet',shts(1));
new_details{1,2} = [tBC;readtable(fn,opt,'Sheet',shts(1))];
new_details{1,2}(:,6)

ans = 25x1 table
      p   
    ______

    3.9627
    11.701
    24.878
       NaN
       NaN
       NaN
       NaN
       NaN
       NaN
       NaN
       NaN
       NaN
       NaN
       NaN
       NaN
       NaN

% Test 2
fn        = 'IMPORT_Network5.xlsx';        
tBC       = [];
opt       = detectImportOptions(fn);
shts      = sheetnames(fn);
new_details = cell(length(shts),2);

new_details{1,1} = [shts(1)];
readtable(fn,opt,'Sheet',shts(1));
new_details{1,2} = [tBC;readtable(fn,opt,'Sheet',shts(1))];
new_details{1,2}(:,6)

ans = 12x1 table
        p     
    __________

    {0x0 char}
    {0x0 char}
    {0x0 char}
    {0x0 char}
    {0x0 char}
    {0x0 char}
    {0x0 char}
    {0x0 char}
    {0x0 char}
    {0x0 char}
    {0x0 char}
    {0x0 char}

[Stephen23]

Set the variable type to numeric:

https://www.mathworks.com/help/matlab/ref/matlab.io.text.delimitedtextimportoptions.setvartype.html

Answer 1

[Torsten]

It seems that columns that contain numbers are interpreted as numeric, and missing data are replaced by NaN, whereas empty columns or columns with character entries are interpreted as char, and missing data are replaced by empty cells.

[Walter Roberson]

If I recall correctly, a completely empty column is normally treated as text.

You can detectImportOptions and set the appropriate variable types to numeric.