A few days I wrote a post about how to convert an cell address into coordinates (column number, and row number). I am working a quick Python script for work to import CSV data into a DB. In order to allow the user the ability to enter either the column number or the column name I used the following function to convert column names into number:
def colNameToNum(name):
pow = 1
colNum = 0
for letter in name[::-1]:
colNum += (int(letter, 36) -9) * pow
pow *= 26
return colNum
As you can see, all you have to do is pass it the name of the column (letters) and the correspond column number will be returned. Have fun! š
3 Comments
Chris · May 1, 2014 at 6:51 PM
We can manipulate values or anything else in Excel spreadsheets using VBA. So why shoud we use Python instead VBA? I have MS Office with Excel, and I can write some simple macros in VBA, so I see no need to learn Python for making scripts/macros. What is the advantage of using Python instead of VBA?
Chris West · May 1, 2014 at 9:33 PM
I didn’t mean to make you think that this is an alternative to using VBA to manipulate data in Excel. Definitely use VBA or even better yet, C# if you need it to be fast. This is really an exercise in Python to show how to convert column names in the event that you are developing an app that has Excel-like column naming.
Chris · May 2, 2014 at 4:48 AM
Thank You for the answer. I was wrong, I thought it was something like suggestion to replace VBA with Python. Now everything is clear for me.