Matlab not recognizing data is scientific notation?

Okay, I don't have the best handle on wrangling data types in Matlab, but to me it looks like Matlab is just not recognizing that I have a selection of numbers of which some are written in scientific notation.

My data comes in like this:(see confusion document attached), where some numbers are written in scientific notation and some are not. Excel seems to be able to recognize the scientific numbers as numbers and offers to change the data type if I click on the cells. However, Matlab just reads in the data (i'm using readtable) as characters(I think) and I cannot figure out how I can make matlab see the table as numerical values.

I've been running around the forums for a while but I can't see what I'm missing here or a previously asked question that helps me.

My goal here is that I am coalescing the data of many files(600+) that are like confusion.xlsx into one larger file, but needs to be written into this larger file as a number, not as a character.

[Kathryn Vernon]

I figured it out! For future users, here's what I did:

https://www.mathworks.com/help/matlab/ref/readtable.html#input_argument_namevalue_d119e887641

opts = detectImportOptions('confusion.xlsx')

opts = setvartype(opts,{'Var3'},'double')

a=readtable('confusion.xlsx',opts)

[dpb]

Huh. That's definitely a PIT proverbial appendage; that would seem worthy of an enhancement request/almost bug report.

I even resaved the spreadsheet after converting the data format storage to numeric instead of general and it still couldn't recognize it. That's just not nice for a scientific/engineering toolset.

Answer 1

[Kiran Felix Robert]

Hi Kathryn,

In your excel file - 'confusion.xlsx', I observed that the numeric data was stored as text data.

There was also a warning/error shown in the excel.

After converting the cells to store data as a number/in numeric format (by using Convert to Number option in Excel), readtable reads it as a numeric value (by default).

Refer the readtable documentation as it mentions that the appropriate data-type of the first cell is used for the entire column, since the cell format was set as text data, MATLAB reads the entire column as text (string) data.

[dpb]

As noted, I resaved the spreadsheet with the format as NUMERIC and got the same result...what is different in that case...

[Kathryn Vernon]

I had assumed that some editing within excel would fix Matlab reading the data as characters. That is a solution, but not a feasible one since I have 600+ files to do this process with.

Thanks for your answer, but I figured out my question(see my comment)