Read in numbers as text from excel

I have a spreadsheet with a mix of numbers and text. However, I need to read the numbers in as text to preserve the exact formatting used in the excel spreadsheet.The readtable(), readcell(), and xlsread() functions all automatically convert the numbers to a double format, even if it is formatted as text in the source spreadsheet. As a result, formatting information (e.g. scientific notation vs decimal) is lost.

I tried using detectImportOptions, then setting the VariableType, but they're still getting converted to double:

filename = 'Test_File.xlsx';
opts = detectImportOptions(filename);
opts = setvartype(opts,'char'); % Set all variable types to char

raw_data = readcell(filename,opts); % Numbers here are still converted to double

Is there a way to keep all excel data as text while reading it in to Matlab? Thanks for your help.

[Stephen23]

When I try it, one cell formatted as text is imported as char:

[N,T,R] = xlsread('test.xlsx','','','basic')

N =

     []

T = 1×1 cell array
    {'1.2345e-6'}

R = 1×1 cell array
    {'1.2345e-6'}

C = readcell('test.xlsx')

C = 1×1 cell array
    {'1.2345e-6'}

Are all of the cells text, or only some of them? If mixed: are data types consistent within each column? Please upload a sample file by clicking the paperclip button.

Are you using Windows with MS Office installed?

Note that if you want reliable data storage/transfer, avoid MS Excel formats:

https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates

[Blake]

I do have excel installed, and I wish I could avoid Excel in this case! I've attached the example. After having expanded my example, I see that this is partially an excel issue - one of the numbers is successfully imported as text, but two others are not. They are still being treated by excel as numbers, despite having the text format selected. B2 in this example is the particular problem. Though I suppose by nature of it not being treated as text, we can assume it's a decimal. I can work around that, though it would be a pain.

I assumed I was doing something simple wrong, since the import options seem to give you the choice of forcing it to text. Instead they don't do anything, as far as I can tell.

[Stephen23]

"I do have excel installed, and I wish I could avoid Excel in this case!"

If you are using XLSREAD then use BASIC mode, as my last comment shows.

Answer 1

[Wan Ji]

IF in excel, a number with high precision is not stored with text format, it cannot be read by readcell with string format for high precision purpose but only be read with double precision, actually excel does the same way with matlab. So why do you force matlab to do that?

When you store a high precision number with text format in excel. then, matlab can read it as a string.

Usually when we write a high precision number in excel, we need to write '3.1415926535897932384626433832795 rather than directly write 3.1415926535897932384626433832795. Because the latter will lose precision in excel.

[Blake]

The number is stored in a text format in excel. Matlab still converts it into a number, despite the steps outlined in the question. It doesn't have the lead apostrophe, but the fields are formatted as text.

[Wan Ji]

you can use istext function in the excel to detect whether the fields are formatted as text as you said