convert number to unique 2 digit alpha

[pcs]

no sure i can even explain this...which is probably why i can't figure it out

i have a group of numbers that basically range from 1 to 600, but not all-inclusive. ie, there are gaps.

i need find a way to represent each number with a unique 2 digit alpha field. ie, convert 001 to AA, and maybe 202 to, say, HB.

this is for a one-time deal to create a file to update info on a ISAM server. so any brute-force suggestions, VBA or query are welcomed.

anybody got any thoughts?

al




[This message has been edited by pcs (edited 02-10-2002).]

 

[Fornatian]

al,

sent you an email, works like this,
1st Digit
1-26 = A
2-52 = B

2nd Digit
1 = A
2 = B


Add the two together to get the number.

Does that help?

 

[pcs]

ian,

---you wrote---
Does that help?
---------------

are you jesting? i was just looking for a place to 'get my shovel in the ground' !

lovely,indeed!

thank you so much!

i owe you one...

al

 

[Alexandre]

Give a try to:

Public Function Base10ToBaseN(iNumber As Double, iBase As Integer) As String


Base10ToBaseN = ""
If iNumber < iBase Then
        Base10ToBaseN = Chr(65 + iNumber) 'chr(65)=A = 0 base N
Else:
    Base10ToBaseN = Base10ToBaseN(iNumber / iBase, iBase) & Chr(65 + iNumber Mod iBase)
End If
End Function


Public Function WrapBaseN(strBaseN As String) As String


If Len(strBaseN) = 1 Then
    WrapBaseN = "A" & strBaseN
Else:
    WrapBaseN = strBaseN
End If
End Function

Use like this:

WrapBaseN(base10tobasen(452,26))

This is inspired from base 10 to BaseN conversion. In the above example (base 26), 0=(A)A, 25=(A)Z, 26=BA, ... 650 = ZA (maximum in base 26 if you want to keep 2 digits).
The first function (recursive) does the conversion, the second just wraps the results to avoid getting 1 digit for low numbers.

Hope this helps

Alex

[This message has been edited by Alexandre (edited 02-10-2002).]

 

[pcs]

alex,

elegant solution...will file it in case i need to ever do this again!

i couldn't see ian's excel formula as it was in a later version than i have. but i was able to copy his sheet1 in xlview and paste it and create my update query.

all's well that ends well

this board is a GREAT resource...

al

 

[Fornatian]

I actually didn't have any formula but after having a play I came up with this for 'fun'

=IF(A2<=26,"A",CHOOSE(SUM(A2-MOD(A2,26))/26,"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"))&CHOOSE(IF(A2<=26,A2,MOD(A2,2 6)+1),"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")

Where A2 is the cell with the number in.

 

[pcs]

i have been promising myself for years to learn how to use mod().

someday, i going to do it!

to alex.....
i think i follow your code, but in:
WrapBaseN(base10tobasen(452,26))
what does the 452 represent?
-------------

so damn much to learn; so damn little time to learn it

thanks again alex and ian.

al


[This message has been edited by pcs (edited 02-11-2002).]

 

[Alexandre]

452 is whatever number you would like to code using two digits. I used Base 26 since it allows enough two-digits combinations to cover your 1..600 range of numbers.

Note that beyond base 26, my function would still work but would come short of alphabet letters and would use the following ASCII chars for coding (I believe: [ ] ...).

Alex

[This message has been edited by Alexandre (edited 02-11-2002).]

 

[pcs]

ok i got it!

thanks again...this one made a little brain-dead

al