How to preserve the percent character '%' when importing data from excel ?

Hi, I am trying to import an excel data using xlsread. But it seems matlab cannot distinguish a cell '20%' from another cell '0.2', cuz both of them will be 'automatically' transformed to '0.2000' during the importing. Here is my issue: Even though they are numerically the same, but the '%' has a special meaning in my context which i intend to identify. Thus, is there a way to preserve the percent character '%' during the importing process?

(Presumably, i can replace '20%' with '20% bla' by search/replace '%' with '% bla' in excel, and do the importing to matlab next. But...it does not look very promising, cuz i have hundreds of excel files)

thanks a lot in advance for your help!

Answer 1

[Image Analyst]

You're going to have to use ActiveX to query Excel and ask it the format of the cell. If the cell is general or some other format, leave it alone and it will come in as 0.2. If the format is Percent, then you can either (1) use ActiveX to convert it to a text string using copy/pase (I think) and then change the format to text, or (2) bring it into MATLAB as a number 0.2 but then convert it to a string using sprintf(). I think (2) wold be the easier route. I don't know the exact ActiveX commands to query the format of the cell - you can look it up as easy as I can.

[Anthony]

thanks! seems there is no way I can get an easier solution...cuz ActiveX is always not easy for me... so, just to confirm, there is no option in the 'xlsread' or 'importdata' that can preserve the % symbol. thanks again.

[Image Analyst]

Correct. Because the real content of the cell is a number, and the percent is just some extra decoration/formatting added by Excel to make it look pretty upon display.

[Anthony]

OK, for others that are interested in this topic. Here is what I did: (adding this part significantly slows down the whole process. maybe it is not the most efficient way, but it works.)

% YOURFILENAME is your own file name, e.g., 'D:\Data\XXX.xlsx'.
exl = actxserver('excel.application');
exl.Visible=0;
exlWkbk = exl.Workbooks;
exlFile = exlWkbk.Open(YOURFILENAME);
exlSheet1 = exlFile.Sheets.Item('Sheet1');
% 'Sheet1' is your target sheet in YOURNAMEFILE
       C=cell(exlSheet1.usedrange.rows.count,exlSheet1.usedrange.columns.count);
  for j=1:size(C,2)
     for i=1:size(C,1)
        if strcmpi(exlSheet1.Range([char(double('A')-1+j)',...
             num2str(i)]).NumberFormat','0%')
           C{i,j}=[num2str(exlSheet1.Range([char(double('A')-1+j)',...
                     num2str(i)]).Value*100),'%'];
        else
           C{i,j}=num2str(exlSheet1.Range([char(double('A')-1+j)',...
                     num2str(i)]).Value);
        end
     end
  end
exl.Quit;
exl.delete;

[Image Analyst]

It's slow because you're launching Excel twice and shutting it down twice. You don't need to do that. Don't call xlsread() if you're doing ActiveX - there's no point. Just get C via ActiveX and it will be much faster - at least twice as fast (half the time).

[Anthony]

thanks for pointing out. updated, although those 'sheet.usedrange.rows.count' is not quite reliable. indeed, faster (not fast enough though...) :P

[Image Analyst]

Yeah, I've noticed that too. It seems like if you have something and then deleted it, it doesn't always seem to know where the end of the data is. Like if your last cell is Z100 and then you delete it so that your new lower right cell is M50, it still thinks the last cell is Z100 - row 100 instead of row 50. Maybe there's a property other than usedrange that is more accurate - I don't know.