{"id":2551,"date":"2010-04-16T11:22:51","date_gmt":"2010-04-16T11:22:51","guid":{"rendered":"https:\/\/blogs.mathworks.com\/pick\/2010\/04\/16\/from-excel-column-labels-to-numbers\/"},"modified":"2018-04-04T11:38:34","modified_gmt":"2018-04-04T15:38:34","slug":"from-excel-column-labels-to-numbers","status":"publish","type":"post","link":"https:\/\/blogs.mathworks.com\/pick\/2010\/04\/16\/from-excel-column-labels-to-numbers\/","title":{"rendered":"From Excel Column Labels to Numbers"},"content":{"rendered":"<div class=\"alert alert-info\"> <span class=\"alert_icon icon-alert-info-reverse\"><\/span><p class=\"alert_heading\"><strong>Note<\/strong><\/p><p>The file submission referenced in this post is no longer available on File Exchange.<\/p><\/div>\r\n\r\n<div xmlns:mwsh=\"https:\/\/www.mathworks.com\/namespace\/mcode\/v1\/syntaxhighlight.dtd\" class=\"content\">\r\n   <p><a href=\"https:\/\/www.mathworks.com\/matlabcentral\/fileexchange\/authors\/15007\">Jiro<\/a>'s pick this week is Excel Column Conversion by <a href=\"https:\/\/www.mathworks.com\/matlabcentral\/fileexchange\/authors\/84146\">Mike Sheppard<\/a>.\r\n   <\/p>\r\n   <p>Oftentimes, I work with Excel files. I read from and write to them using <tt><a href=\"https:\/\/www.mathworks.com\/help\/releases\/R2010a\/techdoc\/ref\/xlsread.html\">xlsread<\/a><\/tt> and <tt><a href=\"https:\/\/www.mathworks.com\/help\/releases\/R2010a\/techdoc\/ref\/xlswrite.html\">xlswrite<\/a><\/tt>, respectively. Both of these functions have an option to specify the range of the worksheet to access, and this is especially\r\n      useful when I am trying to load in one section at a time or append data to the same file. However, to do that I need to specify\r\n      the columns using Excel's column naming convention. For example, 5th column is \"E\" and 28th column is \"AB\".\r\n   <\/p>\r\n   <p>Mike's function <tt>ExcelCol<\/tt> makes this conversion very simple:\r\n   <\/p><pre style=\"background: #F9F7F3; padding: 10px; border: 1px solid rgb(200,200,200)\">ExcelCol(5)\r\nExcelCol(28)<\/pre><pre style=\"font-style:oblique\">ans = \r\n    'E'\r\nans = \r\n    'AB'\r\n<\/pre><p>Now, my code for reading in the first 50 rows from specific columns (columns 120 to 150) of an Excel file looks like this:<\/p><pre style=\"background: #F9F7F3; padding: 10px; border: 1px solid rgb(200,200,200)\">colNames = ExcelCol([120, 150])\r\nrng = sprintf(<span style=\"color: #A020F0\">'%s1:%s50'<\/span>, colNames{:})\r\ndata = xlsread(<span style=\"color: #A020F0\">'datafile.xlsx'<\/span>, rng);<\/pre><pre style=\"font-style:oblique\">colNames = \r\n    'DP'\r\n    'ET'\r\nrng =\r\nDP1:ET50\r\n<\/pre><p>This function works the other way as well, going from a column label to a number:<\/p><pre style=\"background: #F9F7F3; padding: 10px; border: 1px solid rgb(200,200,200)\">ExcelCol(<span style=\"color: #A020F0\">'ABC'<\/span>)<\/pre><pre style=\"font-style:oblique\">ans =\r\n   731\r\n<\/pre><p><b>Comments<\/b><\/p>\r\n   <p>Give this a try and <a href=\"https:\/\/blogs.mathworks.com\/pick\/?p=2551#respond\">tell us<\/a> what you think.\r\n   <\/p><script language=\"JavaScript\">\r\n<!--\r\n\r\n    function grabCode_fd7e91984f7e4d44b5e91cf12eb35a30() {\r\n        \/\/ Remember the title so we can use it in the new page\r\n        title = document.title;\r\n\r\n        \/\/ Break up these strings so that their presence\r\n        \/\/ in the Javascript doesn't mess up the search for\r\n        \/\/ the MATLAB code.\r\n        t1='fd7e91984f7e4d44b5e91cf12eb35a30 ' + '##### ' + 'SOURCE BEGIN' + ' #####';\r\n        t2='##### ' + 'SOURCE END' + ' #####' + ' fd7e91984f7e4d44b5e91cf12eb35a30';\r\n    \r\n        b=document.getElementsByTagName('body')[0];\r\n        i1=b.innerHTML.indexOf(t1)+t1.length;\r\n        i2=b.innerHTML.indexOf(t2);\r\n \r\n        code_string = b.innerHTML.substring(i1, i2);\r\n        code_string = code_string.replace(\/REPLACE_WITH_DASH_DASH\/g,'--');\r\n\r\n        \/\/ Use \/x3C\/g instead of the less-than character to avoid errors \r\n        \/\/ in the XML parser.\r\n        \/\/ Use '\\x26#60;' instead of '<' so that the XML parser\r\n        \/\/ doesn't go ahead and substitute the less-than character. \r\n        code_string = code_string.replace(\/\\x3C\/g, '\\x26#60;');\r\n\r\n        author = 'Jiro Doke';\r\n        copyright = 'Copyright 2010 The MathWorks, Inc.';\r\n\r\n        w = window.open();\r\n        d = w.document;\r\n        d.write('<pre>\\n');\r\n        d.write(code_string);\r\n\r\n        \/\/ Add author and copyright lines at the bottom if specified.\r\n        if ((author.length > 0) || (copyright.length > 0)) {\r\n            d.writeln('');\r\n            d.writeln('%%');\r\n            if (author.length > 0) {\r\n                d.writeln('% _' + author + '_');\r\n            }\r\n            if (copyright.length > 0) {\r\n                d.writeln('% _' + copyright + '_');\r\n            }\r\n        }\r\n\r\n        d.write('<\/pre>\\n');\r\n      \r\n      d.title = title + ' (MATLAB code)';\r\n      d.close();\r\n      }   \r\n      \r\n-->\r\n<\/script><p style=\"text-align: right; font-size: xx-small; font-weight:lighter;   font-style: italic; color: gray\"><br><a href=\"javascript:grabCode_fd7e91984f7e4d44b5e91cf12eb35a30()\"><span style=\"font-size: x-small;        font-style: italic;\">Get \r\n            the MATLAB code \r\n            <noscript>(requires JavaScript)<\/noscript><\/span><\/a><br><br>\r\n      Published with MATLAB&reg; 7.10<br><\/p>\r\n<\/div>\r\n<!--\r\nfd7e91984f7e4d44b5e91cf12eb35a30 ##### SOURCE BEGIN #####\r\n%%\r\n% <https:\/\/www.mathworks.com\/matlabcentral\/fileexchange\/authors\/15007\r\n% Jiro>'s pick this week is\r\n% <-excel-column-conversion\r\n% Excel Column Conversion> by \r\n% <https:\/\/www.mathworks.com\/matlabcentral\/fileexchange\/authors\/84146\r\n% Mike Sheppard>.\r\n%\r\n% Oftentimes, I work with Excel files. I read from and write to them using\r\n% |<https:\/\/www.mathworks.com\/help\/releases\/R2010a\/techdoc\/ref\/xlsread.html\r\n% xlsread>| and\r\n% |<https:\/\/www.mathworks.com\/help\/releases\/R2010a\/techdoc\/ref\/xlswrite.html\r\n% xlswrite>|, respectively. Both of these functions have an option to\r\n% specify the range of the worksheet to access, and this is especially\r\n% useful when I am trying to load in one section at a time or append data\r\n% to the same file. However, to do that I need to specify the columns using\r\n% Excel's column naming convention. For example, 5th column is \"E\" and 28th\r\n% column is \"AB\".\r\n%\r\n% Mike's function |ExcelCol| makes this conversion very simple:\r\n\r\nExcelCol(5)\r\nExcelCol(28)\r\n\r\n%%\r\n% Now, my code for reading in the first 50 rows from specific columns\r\n% (columns 120 to 150) of an Excel file looks like this:\r\n\r\ncolNames = ExcelCol([120, 150])\r\nrng = sprintf('%s1:%s50', colNames{:})\r\ndata = xlsread('datafile.xlsx', rng);\r\n\r\n%%\r\n% This function works the other way as well, going from a column label to a\r\n% number:\r\n\r\nExcelCol('ABC')\r\n\r\n%%\r\n% *Comments*\r\n%\r\n% Give this a try and <https:\/\/blogs.mathworks.com\/pick\/?p=2551#respond tell\r\n% us> what you think, or leave a comment for Mike on the  \r\n% <-excel-column-conversion\r\n% submission page>.\r\n##### SOURCE END ##### fd7e91984f7e4d44b5e91cf12eb35a30\r\n-->","protected":false},"excerpt":{"rendered":"<p> NoteThe file submission referenced in this post is no longer available on File Exchange.\r\n\r\n\r\n   Jiro's pick this week is Excel Column Conversion by Mike Sheppard.\r\n   \r\n   Oftentimes, I work with... <a class=\"read-more\" href=\"https:\/\/blogs.mathworks.com\/pick\/2010\/04\/16\/from-excel-column-labels-to-numbers\/\">read more >><\/a><\/p>","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/blogs.mathworks.com\/pick\/wp-json\/wp\/v2\/posts\/2551"}],"collection":[{"href":"https:\/\/blogs.mathworks.com\/pick\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.mathworks.com\/pick\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.mathworks.com\/pick\/wp-json\/wp\/v2\/users\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.mathworks.com\/pick\/wp-json\/wp\/v2\/comments?post=2551"}],"version-history":[{"count":5,"href":"https:\/\/blogs.mathworks.com\/pick\/wp-json\/wp\/v2\/posts\/2551\/revisions"}],"predecessor-version":[{"id":9624,"href":"https:\/\/blogs.mathworks.com\/pick\/wp-json\/wp\/v2\/posts\/2551\/revisions\/9624"}],"wp:attachment":[{"href":"https:\/\/blogs.mathworks.com\/pick\/wp-json\/wp\/v2\/media?parent=2551"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.mathworks.com\/pick\/wp-json\/wp\/v2\/categories?post=2551"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.mathworks.com\/pick\/wp-json\/wp\/v2\/tags?post=2551"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}