Returning Alphabet Instead of Number

**shrooms** · 01-10-2009, 04:32 AM

Hello one and all, I understand that the column function returns the number position of the column instead of the alphabet as described in the Excel Help Documentation.

=COLUMN(C10) gives 3

However, the interesting question now is how do I return the alphabet "C" instead of "3".

Any help would be greatly appreciated.

**Shijesh Kumar** · 01-10-2009, 05:00 AM

Try this

Please Login or Register  to view this content.

**DonkeyOte** · 01-10-2009, 05:44 AM

The previous post will only work to Column Z of course.

If you extend beyond that use the Address function in conjunction with Substitite:

=SUBSTITUTE(ADDRESS(1,COLUMN(AA1),2),"$1","")

**martindwilson** · 01-10-2009, 06:24 AM

why would you do that?if you know its column c just use c

**DonkeyOte** · 01-10-2009, 06:33 AM

I think more pertinent question is why you need the letter returned in the first instance ? Column letters are rarely (if ever) required for anything.

This Q arises most commonly in VBA circles as the Macro Recorder tends to imply that Range should be qualified via: Range("A1:D1") style whereas in fact in VBA R1C1 notation is the way to go - eg Range(Cells(1,1),Cells(1,4)) does the same thing and offers greater flexibility to the programmer.

Let us know.

**shrooms** · 01-10-2009, 06:41 AM

martindwilson: Your assumption is based on simple excel work, however, I'm going one step further by employing =concatenate , =match , =column functions into my works.
Also, one of the things that I'm doing involves dragging through the row of cells in order to have the alphabets changed. To just use C is no longer automation because I have to manually input it across the row that results in tediousness.

Shijesh Kumar: thanks! you are a genius. I'm sticking to the code that you have provided.

DonkeyOte: many thanks too though I have not tried the code.

**DonkeyOte** · 01-10-2009, 06:50 AM

As I said you rarely (ever) need the letter -- please post up the formula in which you are utilising said letter value as I suspect it can/should be reworked... it sounds as though it may be ADDRESS that you really need to use ... say you're trying to return the address of where a match is found...

so let's assume:

header criteria in cells -- B1:AA1
row criteria in cells A2:A100

you want to find intersect of header criteria & row criteria and return the cell reference as opposed to the value within said cell:

=ADDRESS(MATCH(row criteria,A2:A100,0)+1,MATCH(hdr criteria,B1:AA1,0)+1)

would return Address of resulting intersect point in terms of $F$10 etc...you can toggle optional Abs_Num parameter of Address to remove $ etc..)

**shrooms** · 01-10-2009, 07:04 AM

DonkeyOte: Thanks for the assistance provided. However, Shijesh Kumar has already provided the solution that I'm looking for. I believe your solution would produce the same answer too. As for the further ramifications, I had like to solve it on my own presuming I don't get stuck.