Numbers to Letters

  • Thread starter Thread starter callieandmark
  • Start date Start date
C

callieandmark

I have a spreadsheet which autonumbers column A. However I would like a
formula or macro to convert this so that 1=A, 2=B etc right up to 11111
= AAAAA, 11112 = AAAAB.
Many Thanks in advance.
 
Try this:

Sub Autonumber()
For Each Cell In Range("autonumber_range").Cells
Newcode = ""
For char = 1 To Len(Cell.Value)
Newcode = Newcode & Chr(Asc(Mid(Cell.Value, char, 1)) + 16)
Next
Cell.Value = Newcode
Next
End Sub

Select the range you want to convert, then run the macro.

- Aidey
 
Sorry - I meant to say "name the range you want to convert as
autonumber_range", rather than "select the range you want to convert".
 
....of course if you want to just convert the selected range use this:

Sub Autonumber()
For Each Cell In Selection
Newcode = ""
For char = 1 To Len(Cell.Value)
Newcode = Newcode & Chr(Asc(Mid(Cell.Value, char, 1)) + 16)
Next
Cell.Value = Newcode
Next
End Sub

Sorry to post in three parts, I'm rushing.

Cheers

Aidey
 
Thanks aidey,
That works brilliantly, just what I was after and thanks for the quick
reply.
Mark
 
Back
Top