# Thinking outside the box

Patron Saint of Lost Yaks

## The Excel Column Name assigment problem

Here is a generic algorithm to get the Excel column name according to it's position. By changing the @Base parameter, you can do this for any sequence according to same style as Excel.

DECLARE @Value BIGINT = 8839,
@Base TINYINT = 26

;WITH cteSequence(Position, Value, Chr)
AS (
SELECT  CAST(LOG(@Value - @Value / @Base) / LOG(@Base) AS INT),
CAST(@Value - 1 AS BIGINT),
CAST(CHAR(65 +(@Value - 1) % @Base) AS VARCHAR(MAX))

UNION ALL

SELECT  Position - 1,
Value / @Base - 1,
CHAR(65 +(Value / @Base - 1) % @Base) + Chr
FROM    cteSequence
WHERE   Position > 0
)
SELECT  Chr
FROM    cteSequence
WHERE   Position = 0

Print | posted on Wednesday, April 28, 2010 3:45 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

## #re: The Excel Column Name assigment problem

Without any context, it is pretty hard to know what this algorithm is useful (or not) for. Are you generating column names to be used in creating an excel spreadsheet? Is @Base the number of colums ? Is @Value a seed (meaning it could be set to anything). An explaination would be helpful unless this is part of a private code library.
5/2/2010 9:11 AM | Nathan Omukwenyi

## #re: The Excel Column Name assigment problem

The original problem was to solve a "bullet numbering" problem in a XML document.
Then I realized this also could be used to mimic the "Excel colum name" assignment.

To do that, use @Base as 26 (as there are 26 letters in alphabet used by Excel.
For Excel versions less than 2007, you can use @Value between 1 and 256. For Excel version 2007 and later, you can use value between 1 and 16384.
5/2/2010 10:52 AM |

## #re: The Excel Column Name assigment problem

Thanks,useful for me.
5/7/2010 5:38 AM | Vampal
Comments have been closed on this topic.