Defining a numeric system

[Mark Ivey]

I am trying to get Excel to build a list for me using a special type of
alpha-numeric system. I would like to be able to fill in the first 3 cells
and then be able to drag it down from the bottom right corner to create a
list. Here is the format I am working with...

It is similar to hex but it uses all the letters from the alphabet. Here is
the layout:

0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O,
P, Q, R, S, T, U, V, W, X, Y, Z, 10, 11, 12, 12, 14, 15, 16, 17, 18, 19, 1A,
1B, 1C, 1D, 1E, 1F, 1G, 1H, 1I, 1J, 1K, 1L, 1M, 1N, 1O, 1P, 1Q, 1R, 1S, 1T,
1U, 1V, 1W, 1X, 1Y, 1Z, 20, 21, etc...

I have looked on building a custom list, but it would not be dynamic as I
would need it as shown above. Is there a method, formula, or macro I could
use to create this type of dynamic list???

Any help is greatly appreciated.

 

[Earl Kiosterud]

Mark,

It sounds as though you're trying to set up a number system with base 36. The thing in the
bottom right corner is called the Fill Handle, but doesn't work with three selected cells,
and I don't know of any way to nudge, coerce, cajole it into doing what you want with a
base-36 number system. .

What is your objective? Do you want to create a list of consecutive numbers using your
number system, using the Fill Handle? I don't think that's going to happen. Do you want to
be able to put in various values using your number system, and then do math on them (add,
subtract, etc.)? Or do you have existing numbers in cells that you want shown in your
number system?

 

[Mark Ivey]

Actually, what I am wanting to do is generate a list that I can then use
with a label making program...

What I didn't mention earlier is that I need it to be 6 characters per
number (i.e. - TUK7EX) and then work from there. I was thinking I could
apply whatever help I got from my original question to this situation.

But I cannot think of how to make the list increment in the format I
mentioned previously.

Any ideas???

TIA,

Mark

 

[Pete_UK]

Put this formula in the cell that you want to start with - this does
not need to be A1, but you must have A1 in the formula (i.e. copy it
directly as shown):

=IF(MOD(INT((ROW(A1)-1)/36),36)<10,MOD(INT((ROW(A1)-1)/36),
36),CHAR(55+MOD(INT((ROW(A1)-1)/36),
36)))&IF(MOD(ROW(A1)-1,36)<10,MOD(ROW(A1)-1,36),CHAR(55+MOD(ROW(A1)-1,36)))

This formula can be copied down for as many rows as you require
(starts again after 1296 rows, which is 36*36), and will give you a 2-
character output, i.e. 00, 01, 02 etc 0A, 0B and so on. If you do not
require the leading zero, then use this formula:

=IF(MOD(INT((ROW(A1)-1)/36),36)<10,IF(MOD(INT((ROW(A1)-1)/36),36)=0,"
",MOD(INT((ROW(A1)-1)/36),36)),CHAR(55+MOD(INT((ROW(A1)-1)/36),
36)))&IF(MOD(ROW(A1)-1,36)<10,MOD(ROW(A1)-1,36),CHAR(55+MOD(ROW(A1)-1,36)))

The formula returns " " instead of a leading zero to help line up the
numbers, but you can make it "" if you want to - copy this down as
required. Then copy the formula from the first cell into the cell next
to it on the same row, e.g. if you had put the formula in C3, then
copy it into D3. Highlight the cell that you have just copied into
together with an adjacent cell (but not the first) and CTRL-H to do a
Find & Replace:

Find what: ROW
Replace with: COLUMN
Click Replace All

Alternatively, you can copy this formula (which you will end up with
when doing Find & Replace) into the cell adjacent to your first cell:

=IF(MOD(INT((COLUMN(B1)-1)/36),36)<10,MOD(INT((COLUMN(B1)-1)/36),
36),CHAR(55+MOD(INT((COLUMN(B1)-1)/36),
36)))&IF(MOD(COLUMN(B1)-1,36)<10,MOD(COLUMN(B1)-1,36),CHAR(55+MOD(COLUMN(B1)-1,36)))

or this one if you do not want the leading zero:

=IF(MOD(INT((COLUMN(B1)-1)/36),36)<10,IF(MOD(INT((COLUMN(B1)-1)/36),
36)=0," ",MOD(INT((COLUMN(B1)-1)/36),
36)),CHAR(55+MOD(INT((COLUMN(B1)-1)/36),
36)))&IF(MOD(COLUMN(B1)-1,36)<10,MOD(COLUMN(B1)-1,36),CHAR(55+MOD(COLUMN(B1)-1,36)))

Again, you can make it "" if you wish, then you can copy this formula
across the columns for as many as you require.

I haven't a clue why you would want such a numbering system, but this
gives it to you.

Hope this helps.

Pete

 

[Pete_UK]

Have just re-read your post - I thought you wanted it to go across as
well as down. Given that you don't, you can ignore the second part of
my post.

I've just seen your response to Earl - hopefully you can see how I
have coped with the most significant digit, and can apply this to more
digits.

Pete

 

[Mark Ivey]

Pete,

Your formula is great!!!

I am not sure I can quite understand how to do this for 6 digits. Can you
assist me any further???

Many thanks in advance,
Mark

 

[Pete_UK]

Thanks for the feedback - glad it helped you.

I am not sure if you want to convert a decimal number into a 6-digit
base-36 number to end up with something like 7U15C9, or if you want to
do it the other way round, i.e. take your 6-digit base-36 number like
TUK7EX and convert that into a decimal number.

Or perhaps you want to do something else entirely.

Please advise - I'll be around for another 30 minutes or so.

Pete

 

[Mark Ivey]

Pete,

What I really want to be able to do is input something like "TUK7EX" in A1
and then have this type of formula in A2 and drag it down so it can advance
on the sequence using A1 as a referenced starting point...

TIA,

Mark

 

[Pete_UK]

Okay, Mark, here's something I prepared earlier ...

Put your starting 6-digit base-36 number in A1, then put this formula
in B1:

=IF(LEN($A1)<7-COLUMN(A$1),0,IF(MID($A1,LEN($A1)-6+COLUMN(A$1),1)<="9",
1*MID($A1,LEN($A1)-6+COLUMN(A$1),
1),CODE(UPPER(MID($A1,LEN($A1)-6+COLUMN(A$1),1)))-55))

Copy the formula from B1 into C1:G1 and put this formula into H1:

=B1*36^5+C1*36^4+D1*36^3+E1*36^2+F1*36+G1

Each of the columns B to G are used to split the number up into its
place values, and then H combines them as a decimal value.

Then put this formula into H2:

=H1+1

and this one into A2:

=B2&C2&D2&E2&F2&G2

and finally this one into B2:

=IF(MOD(INT(($H2)/(36^(6-COLUMN(A$1)))),36)<10,MOD(INT(($H2)/(36^(6-
COLUMN(A$1)))),36),CHAR(55+MOD(INT(($H2)/(36^(6-COLUMN(A$1)))),36)))

Copy B2 into C2:G2

The second row of formulae work in reverse from the first row,
converting a decimal number into a 6-digit base-36 number - the number
is incremented in column H.

Now all you have to do is copy the cells A2:H2 down for as many rows
as you need - your incremented numbers will be in column A.

It may be possible to combine all the formulae from one row into one
massive formula, but I wouldn't advise it - you can always hide
columns B to H if you want to.

Hope this helps.

Pete

 

[Ron Rosenfeld]

Pete,

What I really want to be able to do is input something like "TUK7EX" in A1
and then have this type of formula in A2 and drag it down so it can advance
on the sequence using A1 as a referenced starting point...

TIA,

Mark

Here is a UDF that can convert back and forth between base 10 and base 36. So
you could then use this formula in A2 (and fill down as far as required) to
generate your series.

To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Enter your TUK7EX in A1. Then

A2: =conv(conv(A1,36,10,3)+1,10,36,6)

and fill down as far as needed.

======================================
Function Conv(Figure As String, FromBase As Integer, _
ToBase As Integer, NumberOfDigits As Integer) As String
'(e-mail address removed), October 1999
'=conv(Figure,FromBase,ToBase,NumberOfDigits)
'Example: =conv(1234,6,16,6)
'If NumberOfDigits is set to 0 or fewer digits
'than are in the result, the result will be displayed without
'leading zeroes.
'The setup will convert a number from base 2-36
'to another base 2-36
'If the line "Figure = UCase(Figure)" is deleted, it's possible
'to place lower case letters in Digits to cover base 2-62.
'Please keep the above text, if you pass on this routine.

Dim Digits As String
Dim ToBaseTen As Long
Dim Dummy As Variant
Dim Counter As Integer
Dim Result As String
Conv = "Input error"
Digits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
If ToBase > Len(Digits) Then Exit Function
Figure = UCase(Figure)
For Counter = 1 To Len(Figure)
Dummy = Mid$(Figure, Counter, 1)
If InStr(Left$(Digits, FromBase), Dummy) = 0 Then
Exit Function
Else
ToBaseTen = ToBaseTen + (InStr(Digits, Dummy) - 1) * _
(FromBase ^ (Len(Figure) - Counter))
End If
Next Counter
While ToBaseTen > 0
Result = Mid$(Digits, (ToBaseTen Mod ToBase) + 1, 1) & Result
ToBaseTen = Int(ToBaseTen / ToBase)
Wend
If NumberOfDigits = 0 Or NumberOfDigits < Len(Result) Then
Conv = Result
Else
Conv = Right$(String$(NumberOfDigits - Len(Result), "0") & _
Result, NumberOfDigits)
End If
End Function
================================================
--ron

 

[Mark Ivey]

Outstanding...

Just what I was needing. Thank you very much Pete.

 

[Mark Ivey]

Ron,

This is a super-duper UDF. I will include it with my results for my boss to
see which method he prefers.

Thank you very much for your help on this item.

Mark Ivey

 

[Rick Rothstein \(MVP - VB\)]

Put your starting 6-digit base-36 number in A1, then put this formula

in B1:

=IF(LEN($A1)<7-COLUMN(A$1),0,IF(MID($A1,LEN($A1)-6+COLUMN(A$1),1)<="9",
1*MID($A1,LEN($A1)-6+COLUMN(A$1),
1),CODE(UPPER(MID($A1,LEN($A1)-6+COLUMN(A$1),1)))-55))

Copy the formula from B1 into C1:G1 and put this formula into H1:

=B1*36^5+C1*36^4+D1*36^3+E1*36^2+F1*36+G1

Each of the columns B to G are used to split the number up into its
place values, and then H combines them as a decimal value.

If you want to save some columns, the following single formula produces the
same numerical value that you are eventually getting in H1...

=SUMPRODUCT((SEARCH(MID(A1,ROW(INDIRECT("A$1:A"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyz")-1)*36^(6-ROW(INDIRECT("A$1:A"&LEN(A1)))))

Rick

 

[Ron Rosenfeld]

Ron,

This is a super-duper UDF. I will include it with my results for my boss to
see which method he prefers.

Thank you very much for your help on this item.

Mark Ivey

You're welcome, glad to help. Thanks for the feedback.
--ron

 

[Rick Rothstein \(MVP - VB\)]

As long as your boss might be willing to use a UDF, here is another one for
you to consider (assuming you come back to this thread, that is<g>). It will
add one to any properly constructed "number" in any base between 2 and 36.
The only limit to the number of "digits" in the number is Excel's
restriction on the number of characters that can be placed in a cell. (If
you use the function solely within a VB/VBA procedure, the limit is around
two billion "digits".) So, if your starting number is in A1, then you would
put =AddOne(A1,36) in any cell (although A2 would make the most sense) and
copy down to produce a listing of sequential digits in the specified number
base. When you reach the last possible number in the series, the function
returns all dashes (the number of them being equal to the number of digits
in the number being passed into the function).

Rick

Function AddOne(Number As String, Base As Long) As String
Dim X As Long
Dim Char() As Byte
AddOne = UCase(Number)
Char = StrConv(AddOne, vbFromUnicode)
For X = Len(Number) To 1 Step -1
If AddOne = String(Len(Number), "-") Or _
AddOne = String(Len(Number), 54 + Base + 7 * (Base < 11)) Then
AddOne = String(Len(Number), "-")
Exit Function
ElseIf Base > 10 And Char(X - 1) = 57 Then
Mid(AddOne, X) = "A"
Exit For
ElseIf Char(X - 1) < 54 + Base + 7 * (Base < 11) Then
Mid(AddOne, X) = Chr(Char(X - 1) + 1)
Exit For
Else
Mid(AddOne, X) = "0"
End If
Next
End Function

 

[Mark Ivey]

Thanks Rick,

This looks like another very good alternative. I really appreciate your
input.

Just out of curiosity... How can I change this UDF to work on .... let's say
HEX?

Mark

 

[Mark Ivey]

I just figured out how your UDF works... just use a 16 for HEX.

Thanks again. This UDF may become very useful...


Mark

 

[Rick Rothstein \(MVP - VB\)]

Just out of curiosity... How can I change this UDF to work on .... let's

say HEX?

For HEX, use 16 as the base (2nd argument). Other "standard" possibilities
are 2 for Binary, 8 for Binary and 10 for Decimal,

Rick

 

[Rick Rothstein \(MVP - VB\)]

For HEX, use 16 as the base (2nd argument). Other "standard" possibilities

are 2 for Binary, 8 for Binary and 10 for Decimal,

Duh! 8 for Octal...

Rick

 

[Pete_UK]

You're welcome, Mark - thanks for feeding back.

I see you've had other solutions proposed, as well.

Pete