Change from numbers to letters in Excel? 1

[Zenkai]

I have a string of numbers that I want changed into letters in the same format as Excel's columns. I.E. if the number input is 4, I want to get "D" back. If 27, I want "AA" if 53, "BA" etc. Is there a built-in function for this, or do I need to divide by 26, get the remainder, etc.?

-Nick

 

[Zathras]

It's not pretty, but it works:

=IF(A1>26,CHAR(INT((A1-1)/26)+64),"&quot&CHAR(MOD(A1-1,26)+65)

 

[RobBroekhuis]

Question is - why would you need to? I bet that there is a more elegant solution to the UNDERLYING problem.
Rob

 

[Zathras]

Rob has a good point.

If you are trying to reference cell addresses, you may find the .Offset property useful, for example.

What is the problem you are trying to solve?

 

[Zenkai]

Yes, that's correct. I'm getting a cell address with Target.Column or ActiveCell.Column, and it returns a number. Is there a better way, to get the letters instead?

 

[Zathras]

Use the .Address property.

 

[Zenkai]

Oh, great! That worked like a charm Thanks.

Any way the address can be called without the anchors ($)?

 

[Zathras]

Not that I know of.
Rob?

 

[RobBroekhuis]

Why do you need the address property? If you post a bit of your code showing what you are trying to accomplish, I bet we can help you do it.
Rob

 

[LoNeRaVeR]

Zenkai:

You can use the following to return the relative address without the absolute $ designators.

expression

.Address(RowAbsolute:=False, ColumnAbsolute:=False)

Regards, LoNeRaVeR

 

[Zathras]

Very nice. It helps to study the help file. I wonder how many more properties have parameters that extend their usefulness. (So many properties, so little time.)

Star to LoNeRaVeR

 

[Zenkai]

Sorry Rob! I'm working on the same thing I was in the other thread - I assumed you would infer that - the program to output the Excel changes to a word doc. I wanted the full formula instead of row and column.

This is going to a word doc to have a record of changes made to a workbook, and I figured the anchors would be unnecessary visual clutter. The code above worked great!

Thank-you all so much for the help, I appreciate it!

-Nick