what is next cell column in Excel - combinations/permutations

Question

I need to know column name of a spread sheet with a given starting colum upto n number of columns let say starting column is "AB", I want to know the column (label) after 99 columns.

For those who need Ralph answer in T-SQL flavor

Create FUNCTION fsExcelValueToColum(@value int) returns varchar(3) as
begin
DECLARE @DIGIT INT,@RV VARCHAR(3);set @rv=''
IF @VALUE=0 RETURN @rv
WHILE @VALUE > 0
    BEGIN
     SET @DIGIT = @VALUE % 26 
     IF @DIGIT = 0 
      BEGIN
       set @RV=@RV+'Z'
       set @RV=@RV+dbo.fsExcelValueToColum(@value /26 -1)
       return @rv
      END
      set @rv=@rv+char(@digit+64)
      set @value=(@value-@digit) /26
    END
    return @rv
end
USE [ecs]
GO
/****** Object:  UserDefinedFunction [dbo].[fnExcelColumnToValue]    Script Date: 12/06/2009 10:33:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fnExcelColumnToValue](@Column varchar(3)) returns int as
Begin
--declare @column varchar(10); select @column = 'AC'
declare @value int, @pow int,@i int,@n int
select @pow=1,@VALUE=0,@I=LEN(@COLUMN)
SET @I=LEN(@COLUMN)
WHILE @I >= 1
    BEGIN
     SET @VALUE=@VALUE+@POW*((ASCII(SUBSTRING(UPPER(@COLUMN),@I,1)) - 65) +1)
     SET @POW=@POW *26
     SET @I=@I-1
    END
    return @value
end

Answer 1

Think of the characters A, B, ... , Z as representing the values 1, 2, ... 26 (you can't quite think of this as being base-26 because there is no character representing 0 nor can there be as we don't want, for example, AA to mean 0). So we have to engage in some trickery to deal with this:

static int ColumnToValue(string column) {
    int value = 0;
    int pow = 1;
    for (int i = column.Length - 1; i >= 0; i--) {
        value += pow * (column[i] - 'A' + 1);
        pow *= 26;
    }
    return value;
}

static string ValueToColumn(int value) {
    if (value == 0) {
        return string.Empty;
    }
    StringBuilder sb = new StringBuilder();
    while (value > 0) {
        int digit = value % 26;
        if (digit == 0) {
            sb.Insert(0, 'Z');
            return sb.Insert(0, ValueToColumn(value / 26 - 1)).ToString();
        }
        sb.Insert(0,(char)(digit + 'A' - 1));
        value = (value - digit) / 26;
    }
    return sb.ToString();
}

So that

Console.WriteLine(ValueToColumn(ColumnToValue("AB") + 99));

outputs DW and

Console.WriteLine(ValueToColumn(ColumnToValue("AB") - 2));

outputs Z. Obviously you can wrap this all up in a nice class and make it fluent or what have you.

Explanation: Think, for example, of the value 702 and a usual base-26 notation where there is a digit representing 0 (I will use the notation _ to represent this magical digit so that we don't get confused between the value 0 and the digit 0). Let's try to convert 702 (decimal) to base-26. The usual algorithm is compute 702 % 26 which is 0 so that we would have the last digit as being _. Then we would divide by 26 to obtain 27. We would note that 27 % 26 is 1 so that the penultimate digit is A. Then we would divide by 26 to obtain 1, compute 1 % 26 to obtain 1 and report that the most-significant digit is A. We would return AA_ as the string representing 702 in base-26 with digits (_ = 0, A = 1, ... Z = 26) (check: 1 * 26^2 + 1 * 26^1 + 0 * 26 = 702). For our purposes, this is wrong. We wanted to get back the string ZZ (because we have 26 digits A, B, C, ... Z representing 1, 2, ... 26 (remember, no digit representing the value 0!) so that ZZ = 26 * 26^1 + 26 * 26^0 = 702). It turns out in our system that numbers congruent to 0 modulo 26 should have least-significant digit Z. So this suggests that our algorithm should be check and see if value is congruent to 0 modulo 26. If it is, prepend a Z and then prepend the string representing the value value / 26 - 1. This is the algorithm that you see above.

Answer 2

I wrote this a while ago in Clipper (old XBASE compiler)

  if nColumn <= 26                             // Column 26 or less ?
         retval := chr(nColumn+64)
  else
      temp  := int(nColumn/26)                   // Compute first letter
      remd  := nColumn % 26                      // and second letter and
      retval:= if(remd=0,chr(temp+63)+"Z",;
                 chr(temp+64)+chr(remd+64))
  endif

This works for spreadsheets with up to 256 columns, I am not sure if the formula is the same for columns beyond that. I am not sure what programming language you are using, but the Clipper code is pretty simple...

Answer 3

Here's my tuppence worth (quickly written - I'm sure it can be tidied up):-

    public static string ToAlpha(int index)
    {
        if (index <= 0)
        {
            throw new ArgumentOutOfRangeException("index");
        }

        --index;
        var chars = new List<char>();
        int x = index;
        do
        {
            int r = x % 26;
            chars.Insert(0, Convert.ToChar(65 + r));
            x /= 26;
        } while (x > 26);

        if (x > 0)
        {
            chars.Insert(0, Convert.ToChar(64 + x));
        }

        return new string(chars.ToArray());
    }

    public static int ToNumeric(string index)
    {
        int total = 0;
        var chars = index.ToCharArray();
        int chrIndex = chars.Length - 1;
        foreach (char chr in chars)
        {
            total += (Convert.ToInt32(chr) - 64) * (int)(Math.Pow(26d, (double)chrIndex));
            --chrIndex;
        }

        return total;
    }

Usage

        string input = "AB";
        string output = ToAlpha(ToNumeric(input) + 99);