[Solved] Derive column letter from formula

[masv]

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?

[RoryOF]

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

[JohnSUN-Pensioner]

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)

[karolus]

Hallo

For what do you "need" the Columnletter ?

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

Karo

[JohnSUN-Pensioner]

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

[Charlie Young]

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)

[JohnSUN-Pensioner]

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)

[karolus]

Hallo

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)

Code: Select all

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

Karo

[Charlie Young]

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.

[RoryOF]

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?]

[JohnSUN-Pensioner]

Thinks: does anyone still use EBCDIC?

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

[thomasjk]

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

[MrProgrammer]

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.

Code: Select all

;}0 ¨}0<K } 0&&P } 0f&&\}d¨\}dgT} All bytes are printable chars!AOOl20080808AOOL
Pz/DPz/P--zHDP-PD+//A/zH$N+D/P;N+D-SD/+D//D-z-A/zP*:*:i`jhh`jp0j`0eP/PN;,,rj`0i:
;hj,i:;hjhcj;h,,*/`*ra`l.e$dNz+AAyD+zPN+zDD+zD$D+P/PDPzDD+zPA/+*D:D+zD$-+P/PDP-P
ANAzzAD/zAP+*$$;P-;P$*(C) 2008 BY MrProgrammerAOO-PPPPAOO-AOO-*PPP-%-;+**-PPPNAO
O-AOO-AOO-AOO-AOO-AOO-AOO-PPP-AOO-AOO-AOO-e`;iR`e;`.e;`*e;`*I.;P,DeP`+e;`.rj;%An
e;`*I\;P,CeP/PRHr``icj`ZpfRHHHpFH+HQ(C) 2008 BY MrProgrammerAOO/PPpPAOO/AOO/QPPP
bdbipnicNPPeAOO/AOO/AOO-AOO-AOO/AOO/AOO/PPP%AOO/AOO-AOO/

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.