Convert number to Characters

thomasdk

New Member
Joined
Sep 19, 2014
Messages
2
Hi.
I want to make a counter that counts from 0 to 456.976 (26 * 26 * 26 * 26)
I have made the first tasks in VBS. The number is stored in a textfile. I load the number and place it in a document.
Add one to the number and save it to a textfile. No problem.

The problem is converting the number to Characters (by script):
1 = A
2 = B
3 = C
26 = Z
27 = AA
28 = AB
29 = AC
ETC.
Do you have any good suggestions how to approach This task ?
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

You could use this function:

Code:
Function col_ref(col_index As Integer)

    If col_index >= 1 And col_index <= Columns.Count Then
        col_ref = Columns(col_index).Address(ColumnAbsolute:=False)
        col_ref = Left(col_ref, InStr(col_ref, ":") - 1)
    Else
        col_ref = CVErr(xlErrNum)
    End If

End Function
(Note that if you're calling this function from another VBA procedure then you'll need to have a worksheet active when calling it.)
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Code:
Function getPtrn(ByVal I As Long) As String
Const Letters As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim sRes As String, iLen As Integer, k As Long
iLen = Len(Letters)
sRes = Mid$(Letters, ((I - 1) Mod iLen) + 1, 1)
I = Int((I - 1) / iLen)
Do Until I = 0
    sRes = Mid$(Letters, ((I - 1) Mod iLen) + 1, 1) & sRes
    I = Int((I - 1) / iLen)
Loop
getPtrn = sRes
End Function





Hi.
I want to make a counter that counts from 0 to 456.976 (26 * 26 * 26 * 26)
I have made the first tasks in VBS. The number is stored in a textfile. I load the number and place it in a document.
Add one to the number and save it to a textfile. No problem.

The problem is converting the number to Characters (by script):
1 = A
2 = B
3 = C
26 = Z
27 = AA
28 = AB
29 = AC
ETC.
Do you have any good suggestions how to approach This task ?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,697
Messages
5,470,228
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top