Ask Your Question
0

How to convert a column index to a column name? [closed]

asked 2013-01-22 17:19:59 +0200

Gaffer gravatar image

This seems such an obvious question but my Google skills are letting me down.

How do I convert a column index to a column name? I want a function where I supply zero and it returns the string "A". Two would give me "C". 26 would give me "AA" etc.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-20 19:07:07.820808

1 Answer

Sort by » oldest newest most voted
1

answered 2013-01-23 02:07:27 +0200

m.a.riosv gravatar image

Maybe a formula like this, is what you want:

=MID(ADDRESS(1;index);2;LEN(ADDRESS(1;index))-FIND("$";ADDRESS(1;index);1)-2)

edit flag offensive delete link more

Comments

May be not "index", but "index-1" (if 0=A;2=C;26=AA...)? My variant of the formula is more cumbersome =TRIM(LEFT(SUBSTITUTE(CELL("ADDRESS";OFFSET(A1;0;index)) ;"$";" ");7))

JohnSUN gravatar imageJohnSUN ( 2013-01-23 14:18:52 +0200 )edit
1

Oh! I remembered the another variant of the decision! =SUBSTITUTE(ADDRESS(1;index+1;4);"1";"")

JohnSUN gravatar imageJohnSUN ( 2013-01-23 14:42:09 +0200 )edit

Right @JohnSUN, the simplest the better.

m.a.riosv gravatar imagem.a.riosv ( 2013-01-23 22:24:27 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2013-01-22 17:19:59 +0200

Seen: 1,105 times

Last updated: Jan 23 '13