Alphanumeric number generator between two numbers

newyorknix

New Member
Joined
Aug 28, 2011
Messages
16
I want 10 digit alphanumeric numbers generated between two sets...
For example, from abcj3dw5fg2 to abcj3dw4gf2
It should list all combinations in between.
Is there a macro or any other ways to do it?
Thanks & Regards
Nicks
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
Hi Nicks,

I count 11 characters but I hope the following sample is at least a step in the right direction. This will list them in reverse of what you stated in your post. I believe you should be able to simply sort descending if that's what you need. Of course you could also step backwards through the loops to get it right without sorting.

Hope it helps.

Gary

In a standard module (new workbook):

Code:
Public Sub Test()

Dim iCount As Integer
Dim sBase As String
Dim sTemp As String
Dim sChar(36) As String
Dim oCell As Range

Dim iChar8 As Integer
Dim iChar9 As Integer
Dim iChar10 As Integer
Dim iChar11 As Integer

Set oCell = ActiveSheet.Range("A1")

For iCount = 1 To 10
    sChar(iCount) = Chr(47 + iCount)
Next iCount

For iCount = 11 To 36
    sChar(iCount) = Chr(96 + (iCount - 10))
Next iCount

sBase = "abcj3dw"

For iChar8 = 5 To 6
    For iChar9 = 1 To 36
        For iChar10 = 1 To 36
            For iChar11 = 1 To 36
                sTemp = sBase & sChar(iChar8) & sChar(iChar9) & sChar(iChar10) & sChar(iChar11)
                If sTemp >= "abcj3dw4gf2" And sTemp <= "abcj3dw5fg2" Then
                    oCell.Value = sTemp
                    Set oCell = oCell.Offset(1, 0)
                End If
                
                If sTemp = "abcj3dw5fg2" Then Exit Sub
                
            Next iChar11
        Next iChar10
    Next iChar9
Next iChar8

End Sub
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,379
I started this so I will post what I devised.

Code:
Sub LetterCounting()

Dim startStr As String, endStr As String
Dim i As Long

startStr = "aa"
endStr = "b9"
i = 1

Do Until startStr = endStr
Cells(i, 1).Value = startStr
startStr = newString(startStr)
i = i + 1
Loop
Cells(i, 1).Value = endStr
End Sub
Function newString(startStr As String)
Dim CntStr As String
Dim letter As String
Dim i As Long

CntStr = "abcdefghijklmnopqrstuvwxyz0123456789"

If Right(startStr, 1) = 9 Then
    letter = 9
    i = 0
    Do While Left(letter, 1) = 9
    i = i + 1
        letter = Right(startStr, i + 1)
    Loop
    newString = Left(startStr, Len(startStr) - Len(letter)) & Mid(CntStr, InStr(1, CntStr, Left(letter, 1), vbTextCompare) + 1, 1) & Application.WorksheetFunction.Rept("a", Len(letter) - 1)
Else
    newString = Left(startStr, Len(startStr) - 1) & Mid(CntStr, InStr(1, CntStr, Right(startStr, 1), vbTextCompare) + 1, 1)
End If
End Function

I will use an example to explain what I thought you meant.
Letters go from A - Z, numbers from 0 - 9

by adding the 26 letters and 10 number you have a rotation of 36.
There is a variable type for this but can't remember what it is or whether it's a C# object.

So by 'counting' through these items you get:

aa becomes ab becomes ac........ az becomes a0 becomes a1....... a9 becomes ba etc etc.

By looping from a start String to an End string you will get all possible combinations.

You request would list an obscene number of possibilities if I have you question correct, if not please ignore :D
 

Watch MrExcel Video

Forum statistics

Threads
1,122,461
Messages
5,596,274
Members
414,050
Latest member
Rick Royer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top