Apache OpenOffice Community Forum

I'm trying to 'derive' the Column 'Letter' from a formula.

So, if I know that the Column Number is 5 - how do I 'derive' the Column Letter "E" based on the Column Number?

Can you assist me?

Add 40H to your column number and convert it to a character. Google for ASCII encoding to see why.

Welcome to the forum!
Yes, we can... Try THIS
(Sorry for the brevity of the answer - just now come together teams of France and Ukraine)

Hallo

For what do you "need" the Columnletter ?

If you want to feed =INDIRECT(..) with that, try =OFFSET(...) instead INDIRECT.

Karo

Sometimes the column letter used for the heading: "Option A", "Option B"...

RoryOF's solution will only work if it is a single-letter column.

This should get the column "letters" of any cell in which it is entered:

Yes, my dear friend, you're right! +1
But this record is shorter: I referred to it in his response ... (Now break. Account until 0:0)

Hallo

Charlie Young wrote:RoryOF's solution will only work if it is a single-letter column.

This should get the column "letters" of any cell in which it is entered:

Code: Select all

=LEFT(ADDRESS(ROW();COLUMN();4);SEARCH("[:digit:]";ADDRESS(ROW();COLUMN();4))-1)

Karo

JohnSUN-Pensioner wrote:Yes, my dear friend, you're right! +1
But this record is shorter:

Code: Select all

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

I referred to it in his response ... (Now break. Account until 0:0)

Yes, I missed your solution while I was trying mine.

Charlie Young wrote:RoryOF's solution will only work if it is a single-letter column.

I was (naturally!) leaving the extended case as an exercise for the OP! [Thinks: does anyone still use EBCDIC?]

RoryOF wrote:Thinks: does anyone still use EBCDIC?

I think everyone who was holding a real punched cards can remember what it was

I remember EBCIDIC and punched cards quite well. Hmmmmmm, my age is showing

RoryOF wrote:Thinks: does anyone still use EBCDIC?

Most of my professional experience (30+ years) was on the EBCDIC platform. There we would add X'F0' to the digit to get the corresponding numeric character. Actually, for technical reasons, it was more common to OR the value with X'F0' than to add. For multiple digits, there was a nice 3-hardware-instruction combination to convert all of them at once: CVD/UNPK/OI.

A few years back, for fun, I wrote some 360/370 assembler programs using the restriction that all of the instructions had to use characters from the subset of printable EBCDIC (upper/lower case, digits, and special characters below). This turns out to be rather challenging, since in a typical program most of the opcodes and data references will be unprintable characters. For example, register 11 is not easily accessible since none of the Bx characters are printable and the handy Branch Conditional and Store Multiple instructions are not available since their opcodes are 47 and 90. I was eventually able to write "Hello World" using this restriction. The machine instructions follow. They are, of course, not meaningful for anyone other than people intimately familiar with the 360/370 instruction set or for the hardware itself which will print "Hello World" when the instructions are executed. The first instruction is 5ED0F040 or ";}0 ", an unsigned add incrementing register 13. The program is "reentrant" in mainframe-speak and would run on any hardware back to the 360 model 30.

JohnSUN-Pensioner wrote:I think everyone who was holding a real punched cards can remember what it was :D

I didn't work much with punch cards and unit record equipment, but I still recall the few times when cards got jammed inside a collator and I had to use a card saw to remove the pieces.