I have imported a text file to excel. The file has numeric and text columns. In the numeric column there are some scientific numbers which are only in text format. When I import the excel file to matlab I face with a problem about the scientific numbers which says these are not number! How can I deal with these numbers? Why that has been arised?

The file was at the beginning a result of one sequencing program. Then I added txt suffix to change it into a text format. And finally I opened it in Microsoft excel and saved as xlsx format. Here is how one row of my file looks like in excel sheet:

ENSMUSG00000078645  -   -   ENSMUSG00000078645  Fam63a  TSS68162    3:95085766-95088779 -   -   **6.41727e-317**    0   0.309616    OK

the bold number is one of those scientific numbers.

Here is some part of original text file:

tracking_id class_code  nearest_ref_id  gene_id gene_short_name tss_id  locus   length  coverage    FPKM    FPKM_conf_lo    FPKM_conf_hi    FPKM_status

ENSMUSG00000088390  -   -   ENSMUSG00000088390  U7  TSS3702 1:3668960-3669024   -   -   0   0   0   OK
  • Maybe you could give some more information. For example an excerpt from your text file. Can you import the file directly in matlab or do you have to preprocess it with excel? Which function (function call) do you use to import the excel file (from *.xls, from *.csv or from which format) and what is the exact error message?
    – Tim
    Sep 23, 2013 at 10:25
  • I have edited my question. Please let me know if any info is missing! Sep 23, 2013 at 10:48
  • What I actually meant were some lines from your "txt" file. But converting your data file into a text file is just renaming it. It seams like the file itself does not contain pure numbers. Maybe you can export it in another format (like csv) or you have to write a dedicated function for interpreting the file using matlab.
    – Tim
    Sep 23, 2013 at 10:56
  • I exported it into csv format from unix (tr "\t" ",") but still the same problem! Sep 23, 2013 at 11:33
  • Have you tried loading your (original) file into a matlab cellarray using something like A=textscan(fid,'%s',' ','\n')? You could then use cellfun for further processing. Please add some lines from the original file and the error message matlab gives if it fails.
    – Tim
    Sep 23, 2013 at 11:39

2 Answers 2


Here is a matlab script that imports your example data into a cellarray and then copies its contents to variables named after your headers.

fid  = fopen('tst.tst');
data = textscan(fid,'%s %s %s %s %s %s %s %s %s %d %d %d %s','HeaderLines',2);

mytracking_id         = data{1};
myclass_code          = data{2};
mynearest_ref_id      = data{3};
mygene_id             = data{4};
mygene_short_name     = data{5};
mytss_id              = data{6};
mylocus               = data{7};
mylength              = data{8};
mycoverage            = data{9};
myFPKM                = data{10};
myFPKM_conf_lo        = data{11};
myFPKM_conf_hi        = data{12};
myFPKM_status         = data{13};
clear data

Columns read with %d will become vectors of integers (use %g for floating point numbers). Columns read with %s are cell arrays of string, you probably need to further process these.

I chose to use a prefix my, so you do not override functions like length.

  • Thanks a lot. It works perfectly. I Just think instead of %g you meant %f! But it's exactly what I wanted. Sep 23, 2013 at 12:43
  • I actually meant %g, but %f would probably work as well. According to the standard %f is fixed point notation (like 0.0001), %e is exponential notation (like 1.0e-4) and %g can mean either. I guess the difference is only important when printing and scan functions are more tolerant.
    – Tim
    Sep 23, 2013 at 13:07

The VALUE function in Excel will turn text-formatted scientific notation into the stored number value. For example, if you have the text "4.879E-16" in cell A1, you can use:


To get the value:

  • No, it doesn't work like this. The value remains null or unknown (#VALUE!). Sep 23, 2013 at 10:50
  • Ah! Your example data reveals a clue... 6.41727e-317 is an extremely small number. Excel can only hold 255 digits of information in a cell. Your data has 316 zeroes in front of the significant digit, so I suspect it is literally too small for Excel to work with. Sep 23, 2013 at 10:58
  • I would like to convert these super small numbers into zero. Do you have any suggestion not doing manually? in Matlab I could do it by iscellstr function for each individual cell. I wanted to implement it to all cells by cellfun. But it is not working! I have several of these files so programming in matlab is better than in excel. Sep 23, 2013 at 11:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.