GUIDE editable box, Excel to Matlab Column conversion

As most know Excel uses the naming of columns using A,B,C...and so forth, whereas in Matlab it is numerical 1,2,3... In my program a user can choose ANY excel document and convert it to a .mat(specifically gets the {raw} file which in turn each column will be analyzed. I cannot seem to find the code/logic that when using converts that letter to now Ex. column 2 and saves that column separately so I can make calculations on it. Another example is the user enters A into the editable text box, and the code then takes A knows it is an editable text box in GUIDE the user can enter any column from there Excel sheet Ex. B, and then the code column 1 in Matlab and gets that column and saves it separately. In addition the number of columns will not exceed Z in excel and 26 in Matlab, as well as I am analyzing cell arrays. Thank you so much!!

Answer 1

[Geoff Hayes]

Tessa - if you assume that the Excel columns contain alphabetic characters in the set {A,B,C,...,Z}, then you can try using the following conversion

 function [matCol] = exceToMatlab(excelCol)

 numChars = length(excelCol);

 matCol = 0;

 for k=1:length(excelCol)

    % convert the character to an integer: A->1, B->2, etc.
    charAsInt = int64(upper(excelCol(k))) - 64;  

    % add to our running total
    matCol = matCol + charAsInt * (26 ^ (numChars - k));
 end

In the above, we assume that each character can be mapped to the integers 1 through 26, and then, depending upon which position the character is in, we multiply it by 26 to the exponent of that character position less one. So,

 A  --> 1 * 26 ^ (1 - 1) = 1 * 26^0 = 1
 B  --> 2 * 26 ^ (1 - 1) = 2 * 26^0 = 2
 ...
 Z  --> 26 * 26 ^ (1 - 1) = 26 * 26^0 = 26
 AA --> 1 * 26 ^ (2 - 1) + 1 * 26 ^ (2 - 2) = 1 * 26^1 +  1 * 26^0 = 27
 AB --> 1 * 26 ^ (2 - 1) + 2 * 26 ^ (2 - 2) = 1 * 26^1 +  2 * 26^0 = 28
 etc.

Try the above and see what happens!

[Tessa Aus]

Wow that's very helpful thanks! The only question because I am new is using a function in GUIDE, I am not sure how to embed the function of this type of conversion within the function regarding the editable text. How would I implement this in there to allow the input the user enters such as 'A' in the editable text box would call the function, because I thought from my limited knowledge that a function can only run if you enter the lets say 'A' part into the Command Window.

[Geoff Hayes]

Just call this function as you normally would but from your (GUI) code. Presumably you have some sort of callback that (among other things) reads the string from the edit box

 function someCallback(hObject,eventdata,handles)

 columnAsString = char(get(handles.edit1,'String');

 % convert it to a column number
 columnAsNumber = exceToMatlab(columnAsString);

 % etc.

That should do it!

[Tessa Aus]

Thank you so so much!!

[Tessa Aus]

Quick question there is an issue with the folder system in Matlab. I have added this to the code to save the specific column, but the issue is the data (x, columnAsNumber, columnAsString) gets saved into the file but not the Workspace. The inputs gets saved, but the only way I can find to update and import the data to the workspace is if I double click on each file separately.

    % code
columnAsString = char(get(handles.CL4Latitude,'String'));
columnAsNumber = exceToMatlab(columnAsString);
% guidata(hObject,handles)
% load('columnAsNumber.mat');
set(handles.ErrorCheckBox,'string', columnAsNumber);
save columnAsNumber;
% value2 = evalin('base',columnAsNumber);
x = raw(:,columnAsNumber);
save x;
  end

[Geoff Hayes]

Tessa - are you trying to save the file with a particular name? I see that you have commented out

 load('columnAsNumber.mat');

Were you trying to load some file like 1.mat where this corresponds to column A of your Excel file? I would create the filename as

 filename = sprintf('%d.mat',columnAsNumber);

and then use it however you see fit (to save some data to, or load some data from). Like

 filename = sprintf('%d.mat',columnAsNumber);
 x = raw(:,columnAsNumber);
 save(filename,'x');