Turn a number into an Excel column name
This Perl subroutine converts a number into an Excel column label. For example, 10 is column "J", and 50 is column "AX". Columns go from 1 to 256.#!/home/ben/software/install/bin/perl use warnings; use strict; # Just for testing. Save the output as test.csv and open in Excel. for my $i (1..256) { print ntol($i), ", "; } # Convert a number from 1 to 26 into a letter from A to Z. sub _number_to_letter { my ($number) = @_; if ($number > 26) { die "Number $number out of range"; } my $letter = chr ($number+ord("A")); return $letter; } # Convert a number into an Excel column name. sub ntol { # The maximum number of columns in an Excel worksheet is ... my $max_excel_column = 256; my ($n) = @_; my $l; if ($n >= 1 && $n <= 26) { $l = _number_to_letter ($n - 1); } elsif ($n > 26 && $n <= $max_excel_column) { my $chr1 = _number_to_letter (int (($n - 1)/26 - 1)); my $chr2 = _number_to_letter (($n - 1) % 26); $l = $chr1 . $chr2; } else { die "number $n out of Excel's column range (1-$max_excel_column)"; } return $l; }
Copyright © Ben Bullock 2009-2024. All
rights reserved.
For comments, questions, and corrections, please email
Ben Bullock
(benkasminbullock@gmail.com).
/
Privacy /
Disclaimer