Hi, I am really frustrated about that issue, because it seems to me, that it is really easy, but I dont get it running…
So I try to write some cells in an Excel sheet, in a specific range. The range is supplied to the xlswrite or writetable function in the Excel style, i.e. A1:AB15. I would like to create a string that specifies the range dynamically. So I need a function, that converts numbering (1,2,3 …) to "excel alphabetical numbering" (A,B,C, …, Z, AA, AB…). I tried a lot around with some base 26 and base 27 stuff. But it still doesn't give me satisfying results. In the end, I don't really care anymore about the Excel table numbering stuff, i just want to get this function alphabetStr = num2ExcelAlpha(num) to produce the correct string.
My initial thoughts were: The alphabet is some kind of base 26 enumeration (sorry for the wrong terms but I am an engineering grad, not a maths grad…). So basically I can get the number of digits of my "alphabetical number" with some log to the base 26 stuff. It didn't work for me.
In the end I am counting the digits kind of "manually". I bet there is something easier with logarithms, but I didn't figure it out.
digits = 0;while num > 0 num = num - 26^(digits+1); digits = digits+1;end
Now, that we have the number of digits, I would like to assign every number a string consisting of digits characters:
alph = 'A':'Z' str = [];for i = 1:digits idx = floor(num/26^(digits-i)); if idx == 0 idx = 26; end str = [str alph(idx)]; num = num - floor(num/26^(digits-i)); %probalby *26^(digits-i); probably ceil(); something is wrong here
if num == 0 num = 26^(digits-i-1); endend
You see, this is already really confusing what I did there, but this is already my 50th attempt, and it still doesn't give me the right characters consistantly. Mostly, whether the strings with 'A' or 'Z' are missing, wrong, or the index is 0 and it throws an error.
- Is there an easier solution to this?
- If not included in 1: What did I miss regarding the maths part of this problem?
Thanks a lot for your help!
Best Answer