[TIP]: Esoteric method for calculating Excel column references

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[TIP]: Esoteric method for calculating Excel column references

4D Tech mailing list
I had need to calculate column references for an Excel spreadsheet for
columns greater than 26. You know, so you can come up with the reference
like: A1:AG1.

Fun stuff.

Anyway, the scheme for this is deceptive. A -> Z is easy. But A # 0 or 1
strictly. It's tricky. I suspect this is one reason there are functions in
Excel specifically for doing this sort of thing.

However, we have to write our own. In the event someone else needs this
particular bit of code here is a solution I came up with. It could be used
in other contexts I suppose. You can set the base to something smaller. Try
3, for example.

====================================
$chars:="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

$number:=2000
$base:=26
$reference:=""

If ($number=0)  // there is no zero
  $reference:=""
Else
  While ($number>0)
    $remainder:=$number%$base
    $number:=$number\$base

    If ($remainder=0)     // borrow base from number
      $remainder:=$base
      $number:=$number-1
    End if

    $reference:=$chars[[$remainder]]+$reference

    If ($number>0) & ($number<=$base)
      $reference:=$chars[[$number]]+$reference
      $number:=0
    End if

  End while
End if

ALERT($reference)
=================

Posting this in the hope all this nerdy time isn't completely wasted.

--
Kirk Brooks
San Francisco, CA
=======================

*We go vote - they go home*
**********************************************************************
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: [TIP]: Esoteric method for calculating Excel column references

4D Tech mailing list
Kirk,

Thanks for the contribution.
This will come in handy!

Randy Engle

-----Original Message-----
From: 4D_Tech <[hidden email]> On Behalf Of Kirk Brooks via 4D_Tech
Sent: Saturday, December 1, 2018 3:01 PM
To: 4D iNug Technical <[hidden email]>
Cc: Kirk Brooks <[hidden email]>
Subject: [TIP]: Esoteric method for calculating Excel column references

I had need to calculate column references for an Excel spreadsheet for columns greater than 26. You know, so you can come up with the reference
like: A1:AG1.

Fun stuff.

Anyway, the scheme for this is deceptive. A -> Z is easy. But A # 0 or 1 strictly. It's tricky. I suspect this is one reason there are functions in Excel specifically for doing this sort of thing.

However, we have to write our own. In the event someone else needs this particular bit of code here is a solution I came up with. It could be used in other contexts I suppose. You can set the base to something smaller. Try 3, for example.

====================================
$chars:="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

$number:=2000
$base:=26
$reference:=""

If ($number=0)  // there is no zero
  $reference:=""
Else
  While ($number>0)
    $remainder:=$number%$base
    $number:=$number\$base

    If ($remainder=0)     // borrow base from number
      $remainder:=$base
      $number:=$number-1
    End if

    $reference:=$chars[[$remainder]]+$reference

    If ($number>0) & ($number<=$base)
      $reference:=$chars[[$number]]+$reference
      $number:=0
    End if

  End while
End if

ALERT($reference)
=================

Posting this in the hope all this nerdy time isn't completely wasted.

--
Kirk Brooks
San Francisco, CA
=======================

*We go vote - they go home*
**********************************************************************
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: [TIP]: Esoteric method for calculating Excel column references

4D Tech mailing list
In reply to this post by 4D Tech mailing list

> Le 2 déc. 2018 à 00:00, Kirk Brooks via 4D_Tech <[hidden email]> a écrit :
>
> I had need to calculate column references for an Excel spreadsheet for
> columns greater than 26. You know, so you can come up with the reference
> like: A1:AG1.

Hi Kirk,
I had the same problem in the past… I used a similar solution, but recursive:

++++++
  //Utl_spreadsheetColumn (column_l) -> text
  //convert column number to spreadsheet reference
C_TEXT($0;$out_t)
C_LONGINT($1;$unit_l;$base_l;$column_l)
  //_
$out_t:=""
Case of
        : (Count parameters<1)
                  //nope
        : ($1<1)
                  //nope
        Else
                $column_l:=$1
                $unit_l:=(($column_l-1)%26)+1
                $base_l:=($column_l-1)\26
                If ($base_l>0)
                        $out_t:=Utl_spreadsheetColumn ($base_l)+$out_t
                End if
                $out_t:=$out_t+Char($unit_l+64)
End case
$0:=$out_t
++++++

unit test:
++++++
APPEND TO ARRAY($col_al;1)
APPEND TO ARRAY($ut_at;"a")
APPEND TO ARRAY($col_al;26)
APPEND TO ARRAY($ut_at;"z")
APPEND TO ARRAY($col_al;27)
APPEND TO ARRAY($ut_at;"aa")
APPEND TO ARRAY($col_al;702)
APPEND TO ARRAY($ut_at;"zz")
APPEND TO ARRAY($col_al;703)
APPEND TO ARRAY($ut_at;"aaa")
APPEND TO ARRAY($ut_at;"aaa")
APPEND TO ARRAY($col_al;1024)
For ($i_l;1;Size of array($col_al))
        $ref_t:=Utl_spreadsheetColumn ($col_al{$i_l})
        ASSERT($ref_t=$ut_at{$i_l})
End for
++++++

--
Arnaud de Montard




**********************************************************************
4D Internet Users Group (4D iNUG)
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************