Excel formula: Letter to Number (and vice versa)
December 3, 2014 7:33 AM   Subscribe

I'm working on a formula (not a macro) to convert a Letter (in this case, a column letter) to a Number, and vice versa. So: A = 1, B = 2, C = 3, Z = 26, AA = 27, AB = 28, AC = 29, ZZ = 702, AAA = 703, AAB = 704, AAC = 705, BAA = 1379, etc... Of my three part formula(s), I have the one-letter and two-letter columns working, but I run into issues with the three-letter columns (AAA and beyond).

For one-letter columns, these two formulas work:
=CODE(letter)-64
=CHAR(number+64)

For two-letter columns, these two formulas work:
=((CODE(MID(letter,1,1))-64)*26+1)+(CODE(MID(letter,2,1))-64-1)
=CHAR(INT((number-1)/26)+64)&CHAR((MOD(number-1,26))+65

For three-letter columns, these formulas do NOT work (and I'm struggling to understand why):
=((CODE(MID(letter,1,1))-64)*702+1)+(((CODE(MID(letter,2,1))-64)*26+1)-27)+(CODE(MID(letter,3,1))-64-1)
=CHAR(MOD(INT((number-703)/676),26)+65)&CHAR(MOD(INT((number-27)/26),26)+65)&CHAR(MOD(INT(number-1),26))+65

The first formula there actually returns the correct number from AAA to AAZ, but once it hits ABA, it comes in 52 (which must be 26*2) too high and, frankly, my eyeballs are starting to cross at this point.

If it is of any help, I did write a function in the past to convert a number to a letter. I'd just like to figure out how to get it into a formula. The function is:


Sub ColumnNumberToColumnLetter1()
letter_col_num_2 = 26
letter_col_num_3 = 702
letter_col_num_4 = 18278
letter_col_num_5 = 475254
non_letter_cols = 64

ColNumber = ActiveCell.Column

If ColNumber <= letter_col_num_2 Then
ColLetter = Chr(ColNumber + 64)
ElseIf ColNumber <= letter_col_num_3 Then
ColLetter = Chr(Int((ColNumber - 1) / 26) + 64) & Chr(((ColNumber - 1) Mod 26) + 65)
ElseIf ColNumber <= letter_col_num_4 Then
ColLetter = Chr((Int((ColNumber - 703) / 676) Mod 26) + 65) & Chr((Int((ColNumber - 27) / 26) Mod 26) + 65) & Chr(Int((ColNumber - 1) Mod 26) + 65)
ElseIf ColNumber <= letter_col_num_5 Then
ColLetter = Chr((Int((ColNumber - 18279) / 17576) Mod 26) + 65) & Chr((Int((ColNumber - 703) / 676) Mod 26) + 65) & Chr((Int((ColNumber - 27) / 26) Mod 26) + 65) & Chr(Int((ColNumber - 1) Mod 26) + 65)
Else: _
MsgBox "Out of Range"
Exit Sub
End If

Cells(2, ActiveCell.Column).Value = ColLetter

End Sub


Whew.... anyone want to take a stab at it?
posted by JimBJ9 to Technology (11 answers total) 3 users marked this as a favorite
 
Try =COLUMN(INDIRECT("AAA"&1)), where you use the convenient fact that columns in excel are numbered and lettered exactly the way you need. All you need to do is replace the "AAA" in this formula with the reference to the cell in which your letter combination resides and you're done. This works on my excel up to "XFD" after which you get a reference error.
posted by Mons Veneris at 7:47 AM on December 3, 2014 [1 favorite]


I feel like there should be something you can do with LEN, which returns the number of characters as a numerical value, but I can't put my finger on it.
posted by aosher at 7:48 AM on December 3, 2014


I copied your formula into Excel and ABA came out as 729 which is the right answer.
posted by DanSachs at 7:48 AM on December 3, 2014


Response by poster: Oops, sorry, I meant it returns an incorrect value at BAA. I know why it's doing that, I'm just sketchy on the correct way I should be writing that part of the formula.
posted by JimBJ9 at 7:50 AM on December 3, 2014


If this helps, every time the last two characters are "AA" it returns a value 26 (not 52) too high.
posted by DanSachs at 8:03 AM on December 3, 2014


This is a fun problem. Here's my formula for an arbitrary number of characters (just keep adding terms for the number of characters you need, in your case it seems to be 5):

=(CODE(RIGHT(A1,1))-64) +
((CODE(RIGHT(A1,2))-64) * 26^1 *(LEN(A1)>1)) +
((CODE(RIGHT(A1,3))-64) * 26^2 *(LEN(A1)>2)) +
((CODE(RIGHT(A1,4))-64) * 26^3 *(LEN(A1)>3)) +
((CODE(RIGHT(A1,5))-64) * 26^4 *(LEN(A1)>4)) + ...


What's great is that you only need a single equation for all lengths. Naturally, the caveat is that A1 needs to be all upper case.

I'm sure there exists an even better solution with array formulas.
posted by Mons Veneris at 8:09 AM on December 3, 2014


Response by poster: I've half solved it. Here's the letter to number portion:

=IF(LEN(letter)=1,(CODE(MID(letter,1,1))-64),IF(LEN(letter)=2,((CODE(MID(letter,1,1))-64)*26)+(CODE(MID(letter,2,1))-64),IF(LEN(letter)=3,((CODE(MID(letter,1,1))-64)*702-(26*(CODE(MID(letter,1,1))-64)))+((CODE(MID(letter,2,1))-64)*26)+(CODE(MID(letter,3,1))-64),"-")))

Now to fix the number to letter half...
posted by JimBJ9 at 8:13 AM on December 3, 2014


The code you have increments the numerical value by 702 every time the third letter from the left is incremented. But that's not what should happen; it should increment by 676 = 262. Effectively, you've made a compensating error in the second term that cancels it out when the third character from the left is A.

I think the following will work for all three-letter column names:

==((CODE(MID(letter,1,1))-64)*676)+(((CODE(MID(letter,2,1))-64)*26))+(CODE(MID(letter,3,1))-64) + 1
posted by Johnny Assay at 8:14 AM on December 3, 2014


Response by poster: Good lord... my number to letter formula was erroring out because of a misplaced close parentheses. I am going to bury my head in shame.

In case anyone wants them, these formulas will convert any 1 to 3-digit letter into its respective column number and any number (provided it's less than 16384, the max number of columns in Excel 2007) into a 1-to-3 digit column letter.

=IF(LEN(letter) = 1,(CODE(MID(letter,1,1))-64),IF(LEN(letter) = 2,((CODE(MID(letter,1,1))-64)*26)+(CODE(MID(letter,2,1))-64),IF(LEN(letter) = 3,((CODE(MID(letter,1,1))-64)*702-(26*(CODE(MID(letter,1,1))-64)))+((CODE(MID(letter,2,1))-64)*26)+(CODE(MID(letter,3,1))-64),"-")))

=IF(number < 27,CHAR(number+64),IF(number < 703,CHAR(INT((number-1)/26)+64)&CHAR((MOD(number-1,26))+65),CHAR(MOD(INT((number-703)/676),26)+65)&CHAR(MOD(INT((number-27)/26),26)+65)&CHAR(MOD(INT(number-1),26)+65)))

Thanks for talking me through it!
posted by JimBJ9 at 8:24 AM on December 3, 2014


Similarly, the divisor in the first term of your number-to-column formula should be 676 rather than 702:

=CONCATENATE(CHAR(MOD(INT((B10-703)/676),26)+65),CHAR(MOD(INT((B10-27)/26),26)+65),CHAR(MOD(INT(B10-1),26)+65))

(Apologies for the CONCATENATE thing; I couldn't get the ampersands to work.)

Oh, and note that in your formula, you have

((CODE(MID(letter,1,1))-64)*702-(26*(CODE(MID(letter,1,1))-64)))

which is equivalent to

((CODE(MID(letter,1,1))-64)*(702-26)

which is in turn equivalent to

((CODE(MID(letter,1,1))-64)*676

as I had in my version of the letter-to-number formula.
posted by Johnny Assay at 8:24 AM on December 3, 2014


Alright, here's the holy grail with array formulas. If I don't get best answer for this one or the previous, I give up :)

Put your character sequence (e.g. "ABCDX" ) in cell A1 (as in my previous answer). Then, in another cell, enter the following formula:

=SUM( (CODE(RIGHT(A1,{1;2;3;4;5}))-64) * 26^{0;1;2;3;4} * (LEN(A1)>{0;1;2;3;4} ))

This is an array formula, so you need to press CTRL-SHIFT-ENTER (it should appear surrounded by curly brackets in excel). It works for up to 5 characters, but just keep adding terms to the second argument to the RIGHT() function, the exponent to 26 and the comparator to the LEN() function.

Alternatively if you want a completely unmaintainable version, you can do:

=SUM( (CODE(RIGHT(A1,COLUMN(INDIRECT("C1:C"&LEN(A1),FALSE))))-64) * 26^(COLUMN(INDIRECT("C1:C"&LEN(A1),FALSE))-1) )

(Still an array formula, so make sure to type it in with CTRL-SHIFT-Enter). This one works for any arbitrary length of characters but is very difficult to understand. On my machine it works for strings up to a length of 216 after which you get a floating point overflow.
posted by Mons Veneris at 8:38 AM on December 3, 2014 [1 favorite]


« Older Maker noob - where on the web should a maker...   |   HELP ME UNLOCK THE MYSTERIES OF THE PAST Newer »
This thread is closed to new comments.