File Exchange Pick of the Week

Our best user submissions

From Excel Column Labels to Numbers

Note

The file submission referenced in this post is no longer available on File Exchange.

Jiro's pick this week is Excel Column Conversion by Mike Sheppard.

Oftentimes, I work with Excel files. I read from and write to them using xlsread and xlswrite, respectively. Both of these functions have an option to specify the range of the worksheet to access, and this is especially 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 the columns using Excel's column naming convention. For example, 5th column is "E" and 28th column is "AB".

Mike's function ExcelCol makes this conversion very simple:

ExcelCol(5)
ExcelCol(28)
ans = 
    'E'
ans = 
    'AB'

Now, my code for reading in the first 50 rows from specific columns (columns 120 to 150) of an Excel file looks like this:

colNames = ExcelCol([120, 150])
rng = sprintf('%s1:%s50', colNames{:})
data = xlsread('datafile.xlsx', rng);
colNames = 
    'DP'
    'ET'
rng =
DP1:ET50

This function works the other way as well, going from a column label to a number:

ExcelCol('ABC')
ans =
   731

Comments

Give this a try and tell us what you think.




Published with MATLAB® 7.10

|
  • print

댓글

댓글을 남기려면 링크 를 클릭하여 MathWorks 계정에 로그인하거나 계정을 새로 만드십시오.