Importing data with engineering notation into Matlab

Question

I've got a .xls file and I want to import it into Matlab by xlsread function..I get NaNs for numbers with engineering notation..like I get NaNs for 15.252 B or 1.25 M Any suggestions?

Update: I can use [num,txt,raw] = xlsread('...') and the raw one is exactly what I want but how can I replace the Ms with (*10⁶)?

Answer 1

First you could extract everything from excel in a cell array using

[~,~,raw] = xlsread('MyExcelFilename.xlsx')

Then you could write a simple function that returns a number from the string based on 'B', 'M' and so on. Here is such an example:

function mynumber = myfunc( mystring )
% get the numeric part 
my_cell = regexp(mystring,'[0-9.]+','match');
mynumber = str2double(my_cell{1});
% get ending characters
my_cell = regexp(mystring,'[A-z]+','match');
mychars = my_cell{1};
% multiply the number based on char
switch mychars
    case 'B'
        mynumber = mynumber*1e9;
    case 'M'
        mynumber = mynumber*1e6;
    otherwise
end

end

Of course there are other methods to split the numeric string from the rest, use what you want. For more info see the regexp documentation. Finally use cellfun to convert cell array to numeric array:

my_array = cellfun(@myfunc,raw);