MATLAB: Xlsread converting TRUE to 1

xlsread

Does anyone have a work around for when xlsread reads in the raw data and converts the string TRUE into a 1???

It appears that it is doing automatic conversion to boolean, when I need to keep it as a string/char value. This doesn't happen when there are other characters in the field. I'm storing the data into cell arrays, but cannot seem to preserve a string of TRUE from an Excel spreadsheet.

Thanks,

-Mike

Best Answer

What I found so far:

typing in excel true converts the value to 1 (boolean true) if any numeric or general format is selected
typing in excel true when the format was preselected to text, keeps the string (thus importing with xlsread keeps the string)
having typed true with numeric or general format and then converting to text format does not affect the saved value unless you press F2 then Enter. Unfortunately this last operation is aplpicable to single cells.

The solution:

in Excel, select the whole column with the boolean TRUE or FALSE
under the tab panel Data > Text To Columns > Next > Next
select Text (Column data format) > Finish
use [a,b,raw] = xlsread(...)

Related Solutions

MATLAB: Can MATLAB pre-format individual cells when writing data to an EXCEL spreadsheet

Yes you can. See this method from my Excel class:

%-------------------------------------------------------------------------------------------------------
  % Selects all cells in the current worksheet in the specified range.
  % Horizontally aligns all the specified cell range.
  % horizAlign = 1 for left alignment.
  % horizAlign = 3 for center alignment.
  % horizAlign = 4 for right alignment.
  % Leaves with cell A1 selected.
  function AlignCells(Excel, cellReference, horizAlign, autoFit)
    try
      % Select the range
      Excel.Range(cellReference).Select;
      % Align the cell contents.
      Excel.Selection.HorizontalAlignment = horizAlign;
      Excel.Selection.VerticalAlignment = 2;
      if autoFit
        % Auto fit all the columns.
        Excel.Cells.EntireColumn.AutoFit;
      end
      % Put "cursor" or active cell at A1, the upper left cell.
      Excel.Range('A1').Select;
    catch ME
      errorMessage = sprintf('Error in function AlignCells.\nError Message:\n%s', ME.message);
      fprintf('%s\n', errorMessage);
      WarnUser(errorMessage);
    end % from AlignCells
    return;
  end

MATLAB: How to prevent XLSREAD from converting TRUE to 1 and FALSE to 0 in MATLAB R2011a

This happens due to the functionality of Excel.

1. typing in excel true converts the value to 1 (boolean true) if any numeric or general format is selected

2. typing in excel true when the format was preselected to text, keeps the string (thus importing with xlsread keeps the string)

3. having typed true with numeric or general format and then converting to text format does not affect the saved value unless you press F2 then Enter. Unfortunately this last operation is applicable to single cells.

In order to workaround this follow the steps below:

in Excel, select the whole column with the boolean TRUE or FALSE
under the tab panel Data > Text To Columns > Next > Next
select Text (Column data format) > Finish
Now use [a,b,raw] = xlsread(...)

Best Answer

Related Solutions

MATLAB: Can MATLAB pre-format individual cells when writing data to an EXCEL spreadsheet

MATLAB: How to prevent XLSREAD from converting TRUE to 1 and FALSE to 0 in MATLAB R2011a

Related Question