-1

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 (*106)?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
MDBX
  • 13
  • 4

2 Answers2

0

EDIT:

Matlab does not offer any built-in formatting of strings in engineering format.

Source: http://se.mathworks.com/matlabcentral/answers/892-engineering-notation-printed-into-files

In the source you will find also function which would be helpful for you.

Agnieszka Mikołajczyk
  • 88
  • 10
  • Hi..yes 1.25M stands for 1.25x 10^-6..I've got little experience in programming and I know how to do it the hard way in excel but I want to know whats the trick in matlab! – MDBX Jul 24 '15 at 08:41
  • I see. From my expierience we always called engineering notation numbers in format like : 2.7e-6. I don't know any function that can help you with it. You can write your own function by loading it as a string and changing it. Good luck! – Agnieszka Mikołajczyk Jul 24 '15 at 08:51
  • 2.7e-6 is in scientific notation..thanks but I won't lose hope for a simpler solution..you know its matlab! – MDBX Jul 24 '15 at 08:57
  • I found this topic on mathworks forum: http://se.mathworks.com/matlabcentral/answers/892-engineering-notation-printed-into-files I think it would be useful for you. – Agnieszka Mikołajczyk Jul 24 '15 at 09:22
0

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);
Andrei
  • 98
  • 5
  • Thanks Andrei.. I wanted to know is there a built-in function for formatting it seems there isn't such a F.... – MDBX Jul 24 '15 at 09:50
  • your code works well for a cell array (our imported raw) only if all cells have chars (M or B) what if we have a raw = {'50M','2.22M',20;'5.258B', 50, 10} where there are some elements without char? thanks – MDBX Jul 24 '15 at 11:45
  • You could make an isempty check: `if isempty(my_cell) mychars = ''; else mychars = my_cell{1}; end` The switch will go in the otherwise condition and do nothing with the number. – Andrei Jul 24 '15 at 11:50
  • Thanks a lot for the answer....note that we have 2 kinds of numbers in our cells: {'100', 100 } or '100' and [100] your code works for the first number but not the other..... I think it's a problem in the first regexp function, thank you again. – MDBX Jul 24 '15 at 12:22
  • right, forgot about that case. just put `if isnumeric(mystring) mynumber = mystring; return end` at the very beginning of the function – Andrei Jul 24 '15 at 12:30