Kirk,
Thanks for the contribution.
This will come in handy!
Randy Engle
-----Original Message-----
From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Kirk Brooks via
4D_Tech
Sent: Saturday, December 1, 2018 3:01 PM
To: 4D iNug Technical <4d_tech@lists.4d.com>
Cc: Kirk Brooks <lists.k...@gmail.com>
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:4d_tech-unsubscr...@lists.4d.com
**********************************************************************
**********************************************************************
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**********************************************************************
