Numbers to Letters


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.
 
Ad

Advertisements

A

aidey

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
 
A

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".
 
A

aidey

....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
 
Ad

Advertisements

C

callieandmark

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top