How to deal with scientific numbers in excel or matlab?

Question

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

Answer 1

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);
fclose(fid)

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.

Answer 2

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:

=VALUE(A1)

To get the value:

0.0000000000000004879