CODEWITHSUNDEEP
c#excel
robertkroll
robertkroll

Reputation: 8774

How to convert a column number (e.g. 127) into an Excel column (e.g. AA)

How do you convert a numerical number to an Excel column name in C# without using automation getting the value directly from Excel.

Excel 2007 has a possible range of 1 to 16384, which is the number of columns that it supports. The resulting values should be in the form of excel column names, e.g. A, AA, AAA etc.

Upvotes: 575

Views: 308060

Answers (30)

DatacraftAI
DatacraftAI

Reputation: 157

Here is my solution in python

import math

num = 3500
row_number = str(math.ceil(num / 702))
letters = ''
num = num - 702 * math.floor(num / 702)
while num:
    mod = (num - 1) % 26
    letters += chr(mod + 65)
    num = (num - 1) // 26
result = row_number + ("".join(reversed(letters)))
print(result)

Upvotes: -2

user932708
user932708

Reputation: 89

After looking at all the supplied Versions here, I decided to do one myself, using recursion.

Here is my vb.net Version:

Function CL(ByVal x As Integer) As String
    If x >= 1 And x <= 26 Then
        CL = Chr(x + 64)
    Else
        CL = CL((x - x Mod 26) / 26) & Chr((x Mod 26) + 1 + 64)
    End If
End Function

Upvotes: 3

Tyler
Tyler

Reputation: 1283

Typescript

function lengthToExcelColumn(len: number): string {

    let dividend: number = len;
    let columnName: string = '';
    let modulo: number = 0;

    while (dividend > 0) {
        modulo = (dividend - 1) % 26;
        columnName = String.fromCharCode(65 + modulo).toString() + columnName;
        dividend = Math.floor((dividend - modulo) / 26);
    }
    return columnName;
}

Upvotes: 0

user121385
user121385

Reputation:

Using this in VB.Net 2005 :

Private Function ColumnName(ByVal ColumnIndex As Integer) As String

   Dim Name As String = ""

   Name = (New Microsoft.Office.Interop.Owc11.Spreadsheet).Columns.Item(ColumnIndex).Address(False, False, Microsoft.Office.Interop.Owc11.XlReferenceStyle.xlA1)
   Name = Split(Name, ":")(0)

   Return Name

End Function

Upvotes: 0

Ally
Ally

Reputation: 4942

JavaScript Solution

/**
 * Calculate the column letter abbreviation from a 1 based index
 * @param {Number} value
 * @returns {string}
 */
getColumnFromIndex = function (value) {
    var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
    var remainder, result = "";
    do {
        remainder = value % 26;
        result = base[(remainder || 26) - 1] + result;
        value = Math.floor(value / 26);
    } while (value > 0);
    return result;
};

console.log(getColumnFromIndex(1), "A");
console.log(getColumnFromIndex(26), "Z");
console.log(getColumnFromIndex(27), "AA");
console.log(getColumnFromIndex(52), "AZ");
console.log(getColumnFromIndex(53), "BA");
console.log(getColumnFromIndex(702), "ZZ");
console.log(getColumnFromIndex(703), "AAA");
console.log(getColumnFromIndex(704), "AAB");
console.log(getColumnFromIndex(16384), "XFD");

Upvotes: 2

Erechtheus
Erechtheus

Reputation: 765

T-SQL (SQL SERVER 18)

Copy of the solution on first page

CREATE FUNCTION dbo.getExcelColumnNameByOrdinal(@RowNum int)  
RETURNS varchar(5)   
AS   
BEGIN  
    DECLARE @dividend int = @RowNum;
    DECLARE @columnName varchar(max) = '';
    DECLARE @modulo int;

    WHILE (@dividend > 0)
    BEGIN  
        SELECT @modulo = ((@dividend - 1) % 26);
        SELECT @columnName = CHAR((65 + @modulo)) + @columnName;
        SELECT @dividend = CAST(((@dividend - @modulo) / 26) as int);
    END
    RETURN 
       @columnName;

END;

Upvotes: 0

bpolat
bpolat

Reputation: 3908

Objective-C Implementation :

-(NSString*)getColumnName:(int)n {
     NSString *name = @"";
     while (n>0) {
     n--;
     char c = (char)('A' + n%26);
     name = [NSString stringWithFormat:@"%c%@",c,name];
     n = n/26;
  }    
     return name;
}

SWIFT Implementation:

func getColumnName(n:Int)->String{
 var columnName = ""
 var index = n
 while index>0 {
     index--
     let char = Character(UnicodeScalar(65 + index%26))
     columnName = "\(char)\(columnName)"
     index = index / 26
 }
 return columnName
 }

The answer is based on https://stackoverflow.com/a/4532562/2231118

Upvotes: 0

Ian
Ian

Reputation: 7558

(I realise the question relates to C# however, if anyone reading needs to do the same thing with Java then the following may be useful)

It turns out that this can easily be done using the the "CellReference" class in Jakarta POI. Also, the conversion can be done both ways.

// Convert row and column numbers (0-based) to an Excel cell reference
CellReference numbers = new CellReference(3, 28);
System.out.println(numbers.formatAsString());
        
// Convert an Excel cell reference back into digits
CellReference reference = new CellReference("AC4");
System.out.println(reference.getRow() + ", " + reference.getCol());

Upvotes: 0

lambdapilgrim
lambdapilgrim

Reputation: 1071

Here is how I would do it in Python. The algorithm is explained below:

alph = ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z')
def labelrec(n, res):
    if n<26:
        return alph[n]+res
    else:
        rem = n%26
        res = alph[rem]+res
        n = n/26-1
        return labelrec(n, res)

The function labelrec can be called with the number and an empty string like:

print labelrec(16383, '')

Here is why it works: If decimal numbers were written the same way as Excel sheet columns, number 0-9 would be written normally, but 10 would become '00' and then 20 would become '10' and so on. Mapping few numbers:

0 - 0

9 - 9

10 - 00

20 - 10

100 - 90

110 - 000

1110 - 0000

So, the pattern is clear. Starting at the unit's place, if a number is less than 10, it's representation is same as the number itself, else you need to adjust the remaining number by subtracting it by 1 and recurse. You can stop when the number is less than 10.

The same logic is applied for numbers of base 26 in above solution.

P.S. If you want the numbers to begin from 1, call the same function on input number after decreasing it by 1.

Upvotes: 0

Manuel
Manuel

Reputation:

I'm using this one in VB.NET 2003 and it works well...

Private Function GetExcelColumnName(ByVal aiColNumber As Integer) As String
    Dim BaseValue As Integer = Convert.ToInt32(("A").Chars(0)) - 1
    Dim lsReturn As String = String.Empty

    If (aiColNumber > 26) Then
        lsReturn = GetExcelColumnName(Convert.ToInt32((Format(aiColNumber / 26, "0.0").Split("."))(0)))
    End If

    GetExcelColumnName = lsReturn + Convert.ToChar(BaseValue + (aiColNumber Mod 26))
End Function

Upvotes: 0

Hasan
Hasan

Reputation: 309

I'm trying to do the same thing in Java... I've wrote following code:

private String getExcelColumnName(int columnNumber) {

    int dividend = columnNumber;
    String columnName = "";
    int modulo;

    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;

        char val = Character.valueOf((char)(65 + modulo));

        columnName += val;

        dividend = (int)((dividend - modulo) / 26);
    } 

    return columnName;
}

Now once I ran it with columnNumber = 29, it gives me the result = "CA" (instead of "AC") any comments what I'm missing? I know I can reverse it by StringBuilder.... But looking at the Graham's answer, I'm little confused....

Upvotes: 1

Ian Atkin
Ian Atkin

Reputation: 6346

Here's my super late implementation in PHP. This one's recursive. I wrote it just before I found this post. I wanted to see if others had solved this problem already...

public function GetColumn($intNumber, $strCol = null) {

    if ($intNumber > 0) {
        $intRem = ($intNumber - 1) % 26;
        $strCol = $this->GetColumn(intval(($intNumber - $intRem) / 26), sprintf('%s%s', chr(65 + $intRem), $strCol));
    }

    return $strCol;
}

Upvotes: 1

JRL
JRL

Reputation: 3401

In Delphi (Pascal):

function GetExcelColumnName(columnNumber: integer): string;
var
  dividend, modulo: integer;
begin
  Result := '';
  dividend := columnNumber;
  while dividend > 0 do begin
    modulo := (dividend - 1) mod 26;
    Result := Chr(65 + modulo) + Result;
    dividend := (dividend - modulo) div 26;
  end;
end;

Upvotes: 5

Phoeson
Phoeson

Reputation: 211

This is a javascript version according to Graham's code

function convertColumnNumberToName(columnNumber) {
    var dividend = columnNumber;
    var columnName = "";
    var modulo;

    while (dividend > 0) {
        modulo = (dividend - 1) % 26;
        columnName = String.fromCharCode(65 + modulo) + columnName;
        dividend = parseInt((dividend - modulo) / 26);
    }

    return columnName;
};

function convertColumnNameToNumber() {
    var input = document.getElementById("colNum");
    input.insertAdjacentHTML('afterend', '<div>' + convertColumnNumberToName(input.value) + '</div>');
}
<form name="colToNumConverter">Convert column number to Excel column name:<br>
    <input type="number" name="columnNum" id="colNum" />
    <button type="button" onclick="convertColumnNameToNumber()">Convert</button>
    <br>
    <label id="column"></label>
</form>

Upvotes: 1

phlare
phlare

Reputation: 316

Thanks for the answers here!! helped me come up with these helper functions for some interaction with the Google Sheets API that i'm working on in Elixir/Phoenix

here's what i came up with (could probably use some extra validation and error handling)

In Elixir:

def number_to_column(number) do
  cond do
    (number > 0 && number <= 26) ->
      to_string([(number + 64)])
    (number > 26) ->
      div_col = number_to_column(div(number - 1, 26))
      remainder = rem(number, 26)
      rem_col = cond do
        (remainder == 0) ->
          number_to_column(26)
        true ->
          number_to_column(remainder)
      end
      div_col <> rem_col
    true ->
      ""
  end
end

And the inverse function:

def column_to_number(column) do
  column
    |> to_charlist
    |> Enum.reverse
    |> Enum.with_index
    |> Enum.reduce(0, fn({char, idx}, acc) ->
      ((char - 64) * :math.pow(26,idx)) + acc
    end)
    |> round
end

And some tests:

describe "test excel functions" do
  @excelTestData [{"A", 1}, {"Z",26}, {"AA", 27}, {"AB", 28}, {"AZ", 52},{"BA", 53}, {"AAA", 703}]

  test "column to number" do
    Enum.each(@excelTestData, fn({input, expected_result}) ->
      actual_result = BulkOnboardingController.column_to_number(input)
      assert actual_result == expected_result
    end)
  end

  test "number to column" do
    Enum.each(@excelTestData, fn({expected_result, input}) ->
      actual_result = BulkOnboardingController.number_to_column(input)
      assert actual_result == expected_result
    end)
  end
end

Upvotes: 1

Iwan B.
Iwan B.

Reputation: 4156

Concise and elegant Ruby version:

def col_name(col_idx)
    name = ""
    while col_idx>0
        mod     = (col_idx-1)%26
        name    = (65+mod).chr + name
        col_idx = ((col_idx-mod)/26).to_i
    end
    name
end

Upvotes: 1

grepit
grepit

Reputation: 22382

Most of previous answers are correct. Here is one more way of converting column number to excel columns.

The solution is rather simple if we think about this as a base conversion. Simply, convert the column number to base 26 since there is 26 letters only. Here is how you can do this:

steps:

  • set the column as a quotient
  • subtract one from quotient variable (from previous step) because we need to end up on ascii table with 97 being a.
  • divide by 26 and get the remainder.
  • add +97 to remainder and convert to char (since 97 is "a" in ASCII table)
  • quotient becomes the new quotient/ 26 (since we might go over 26 column)
  • continue to do this until quotient is greater than 0 and then return the result

here is the code that does this :)

def convert_num_to_column(column_num):
    result = ""
    quotient = column_num
    remainder = 0
    while (quotient >0):
        quotient = quotient -1
        remainder = quotient%26
        result = chr(int(remainder)+97)+result
        quotient = int(quotient/26)
    return result

print("--",convert_num_to_column(1).upper())

Upvotes: 1

Maslow
Maslow

Reputation: 18746

F# version of each way

let rec getExcelColumnName x  = if x<26 then int 'A'+x|>char|>string else (x/26-1|>c)+ c(x%26)

pardon the minimizing, was working on a better version of https://stackoverflow.com/a/4500043/57883

and the opposite direction: 
// return values start at 0
let getIndexFromExcelColumnName (x:string) =
    let a = int 'A'
    let fPow len i =
        Math.Pow(26., len - 1 - i |> float)
        |> int
    
    let getValue len i c = 
        int c - a + 1 * fPow len i
    let f i = getValue x.Length i x.[i]
    [0 .. x.Length - 1]
    |> Seq.map f
    |> Seq.sum
    |> fun x -> x - 1

Upvotes: 1

Myforwik
Myforwik

Reputation: 3588

In perl, for an input of 1 (A), 27 (AA), etc.

sub excel_colname {
  my ($idx) = @_;       # one-based column number
  --$idx;               # zero-based column index
  my $name = "";
  while ($idx >= 0) {
    $name .= chr(ord("A") + ($idx % 26));
    $idx   = int($idx / 26) - 1;
  }
  return scalar reverse $name;
}

Upvotes: 4

DataEngineer
DataEngineer

Reputation: 396

Sorry, this is Python instead of C#, but at least the results are correct:

def excel_column_number_to_name(column_number):
    output = ""
    index = column_number-1
    while index >= 0:
        character = chr((index%26)+ord('A'))
        output = output + character
        index = index/26 - 1
                    
    return output[::-1]


for i in xrange(1, 1024):
    print "%4d : %s" % (i, excel_column_number_to_name(i))

Passed these test cases:

  • Column Number: 494286 => ABCDZ
  • Column Number: 27 => AA
  • Column Number: 52 => AZ

Upvotes: 2

Rob Sawyer
Rob Sawyer

Reputation: 2183

NodeJS implementation:

/**
* getColumnFromIndex
* Helper that returns a column value (A-XFD) for an index value (integer).
* The column follows the Common Spreadsheet Format e.g., A, AA, AAA.
* See https://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa/3444285#3444285
* @param numVal: Integer
* @return String
*/
function getColumnFromIndex(numVal){
   var dividend = parseInt(numVal);
   var columnName = '';
   var modulo;
   while (dividend > 0) {
      modulo = (dividend - 1) % 26;
      columnName = String.fromCharCode(65 + modulo) + columnName;
      dividend = parseInt((dividend - modulo) / 26);
   }
   return columnName;
}

function convertNumberToColumnName() {
    var input = document.getElementById("num");
    input.insertAdjacentHTML('afterend', '<div>' + getColumnFromIndex(input.value) + '</div>');
}
<form name="colConverter">Convert column number to Excel column name:
    <input type="number" name="number" id="num" />
    <button type="button" onclick="convertNumberToColumnName()">Convert</button>
    <br>
    <label id="columnName"></label>
</form>

Thanks to Convert excel column alphabet (e.g. AA) to number (e.g., 25). And in reverse:

/**
* getIndexFromColumn
* Helper that returns an index value (integer) for a column value (A-XFD).
* The column follows the Common Spreadsheet Format e.g., A, AA, AAA.
* See https://stackoverflow.com/questions/9905533/convert-excel-column-alphabet-e-g-aa-to-number-e-g-25
* @param strVal: String
* @return Integer
*/
function getIndexFromColumn(val){
   var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', i, j, result = 0;
   for (i = 0, j = val.length - 1; i < val.length; i += 1, j -= 1) {
      result += Math.pow(base.length, j) * (base.indexOf(val[i]) + 1);
   }
   return result;
}

function convertColumnNameToNumber() {
    var input = document.getElementById("colName");
    input.insertAdjacentHTML('afterend', '<div>' + Number(getIndexFromColumn(input.value.toUpperCase())) + '</div>');
}
<form name="colToNumConverter">Convert Excel column name to column number:<br>
    <input type="text" name="columnName" id="colName" />
    <button type="button" onclick="convertColumnNameToNumber()">Convert</button>
    <br>
    <label id="column"></label>
</form>

Upvotes: 1

John
John

Reputation: 151

I discovered an error in my first post, so I decided to sit down and do the the math. What I found is that the number system used to identify Excel columns is not a base 26 system, as another person posted. Consider the following in base 10. You can also do this with the letters of the alphabet.

Space:.........................S1, S2, S3 : S1, S2, S3  
....................................0, 00, 000 :.. A, AA, AAA  
....................................1, 01, 001 :.. B, AB, AAB  
.................................... …, …, … :.. …, …, …  
....................................9, 99, 999 :.. Z, ZZ, ZZZ  
Total states in space:    10, 100, 1000 : 26, 676, 17576  
Total States:...............1110................18278

Excel numbers columns in the individual alphabetical spaces using base 26. You can see that in general, the state space progression is a, a2, a3, … for some base a, and the total number of states is a + a2 + a3 + … .

Suppose you want to find the total number of states A in the first N spaces. The formula for doing so is A = (a)(aN - 1 )/(a-1). This is important because we need to find the space N that corresponds to our index K. If I want to find out where K lies in the number system I need to replace A with K and solve for N. The solution is N = loga(A (a-1)/a +1). If I use the example of a = 10 and K = 192, I know that N = 2.23804… This tells me that K lies at the beginning of the third space since it is a little greater than two.

The next step is to find exactly how far in the current space we are. To find this, subtract from K the A generated using the floor of N. In this example, the floor of N is two. So, A = (10)(102 – 1)/(10-1) = 110, as is expected when you combine the states of the first two spaces. This needs to be subtracted from K because these first 110 states would have already been accounted for in the first two spaces. This leaves us with 82 states. So, in this number system, the representation of 192 in base 10 is 082.

The C# code using a base index of zero is

    private string ExcelColumnIndexToName(int Index)
    {
        string range = string.Empty;
        if (Index < 0 ) return range;
        int a = 26;
        int x = (int)Math.Floor(Math.Log((Index) * (a - 1) / a + 1, a));
        Index -= (int)(Math.Pow(a, x) - 1) * a / (a - 1);
        for (int i = x+1; Index + i > 0; i--)
        {
            range = ((char)(65 + Index % a)).ToString() + range;
            Index /= a;
        }
        return range;
    }

//Old Post

A zero-based solution in C#.

    private string ExcelColumnIndexToName(int Index)
    {
        string range = "";
        if (Index < 0 ) return range;
        for(int i=1;Index + i > 0;i=0)
        {
            range = ((char)(65 + Index % 26)).ToString() + range;
            Index /= 26;
        }
        if (range.Length > 1) range = ((char)((int)range[0] - 1)).ToString() + range.Substring(1);
        return range;
    }

Upvotes: 15

Balaji.N.S
Balaji.N.S

Reputation: 743

Same implementation in Java

public String getExcelColumnName (int columnNumber) 
    {     
        int dividend = columnNumber;   
        int i;
        String columnName = "";     
        int modulo;     
        while (dividend > 0)     
        {        
            modulo = (dividend - 1) % 26;         
            i = 65 + modulo;
            columnName = new Character((char)i).toString() + columnName;        
            dividend = (int)((dividend - modulo) / 26);    
        }       
        return columnName; 
    }

Upvotes: 10

Stephen Fuhry
Stephen Fuhry

Reputation: 13009

... And converted to php:

function GetExcelColumnName($columnNumber) {
    $columnName = '';
    while ($columnNumber > 0) {
        $modulo = ($columnNumber - 1) % 26;
        $columnName = chr(65 + $modulo) . $columnName;
        $columnNumber = (int)(($columnNumber - $modulo) / 26);
    }
    return $columnName;
}

Upvotes: 11

MaVRoSCy
MaVRoSCy

Reputation: 17839

This answer is in javaScript:

function getCharFromNumber(columnNumber){
    var dividend = columnNumber;
    var columnName = "";
    var modulo;

    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = String.fromCharCode(65 + modulo).toString() + columnName;
        dividend = parseInt((dividend - modulo) / 26);
    } 
    return columnName;
}

byId('convertBtn').onclick = function (e) {
    e.preventDefault();
    var i = byId('num'),
        inputVal = i.value;
    
    if (inputVal == "") {
        i.style.backgroundColor = "red";
    } else {
        byId('colName').innerText = getCharFromNumber(Number(inputVal)) + '';
        i.style.backgroundColor = "#fff";
    }
}

function byId(x) {
    return document.getElementById(x);
}
<h1>Number to Excel column name. Press "Run code snippet" to try</h1>

<form name="NumToCol">Column number:
    <input name="colNum" id="num">
    <button id='convertBtn'>Convert</button>
    <br>
    <label id="colName"></label>
</form>

Upvotes: 15

vzczc
vzczc

Reputation: 9380

If anyone needs to do this in Excel without VBA, here is a way:

=SUBSTITUTE(ADDRESS(1;colNum;4);"1";"")

where colNum is the column number

And in VBA:

Function GetColumnName(colNum As Integer) As String
    Dim d As Integer
    Dim m As Integer
    Dim name As String
    d = colNum
    name = ""
    Do While (d > 0)
        m = (d - 1) Mod 26
        name = Chr(65 + m) + name
        d = Int((d - m) / 26)
    Loop
    GetColumnName = name
End Function

Upvotes: 69

RoMa
RoMa

Reputation: 859

Sorry, this is Python instead of C#, but at least the results are correct:

def ColIdxToXlName(idx):
    if idx < 1:
        raise ValueError("Index is too small")
    result = ""
    while True:
        if idx > 26:
            idx, r = divmod(idx - 1, 26)
            result = chr(r + ord('A')) + result
        else:
            return chr(idx + ord('A') - 1) + result
    
    
for i in xrange(1, 1024):
    print "%4d : %s" % (i, ColIdxToXlName(i))

Upvotes: 31

Paul Ma
Paul Ma

Reputation: 469

Another VBA way

Public Function GetColumnName(TargetCell As Range) As String
    GetColumnName = Split(CStr(TargetCell.Cells(1, 1).Address), "$")(1)
End Function

Upvotes: 2

user1098928
user1098928

Reputation: 83

For what it is worth, here is Graham's code in Powershell:

function ConvertTo-ExcelColumnID {
    param (
        [parameter(Position = 0,
            HelpMessage = "A 1-based index to convert to an excel column ID. e.g. 2 => 'B', 29 => 'AC'",
            Mandatory = $true)]
        [int]$index
    );

    [string]$result = '';
    if ($index -le 0 ) {
        return $result;
    }

    while ($index -gt 0) {
        [int]$modulo = ($index - 1) % 26;
        $character = [char]($modulo + [int][char]'A');
        $result = $character + $result;
        [int]$index = ($index - $modulo) / 26;
    }

    return $result;
}

Upvotes: 3

gogo
gogo

Reputation: 1011

Simple and concise JavaScript function that converts column number to a spreadsheet column name.

function column(number)
{
    const name = [];
    for(let n = number - 1; n >= 0; n = Math.floor(n / 26) - 1)
    {
        name.push(String.fromCharCode(65 + n % 26));
    }
    return name.reverse().join("");
};

console.log(column(1), "A");
console.log(column(26), "Z");
console.log(column(27), "AA");
console.log(column(52), "AZ");
console.log(column(53), "BA");
console.log(column(702), "ZZ");
console.log(column(703), "AAA");
console.log(column(704), "AAB");
console.log(column(16384), "XFD");

Upvotes: 0

Related Questions