strange number conversion

[impulse24]

Hi,

I have a customer who is sending me numeric values from 1-234. They then want me to convert those values to alpha & numeric.

1 = 1A
2 = 1B
27 = 2A

The number goes from 1-26 and has A-Z, then when it gets to 27 it goes to 2A - 2Z, and so on. I can't seem to figure out the conversion table. Please help

 

[Golom]

Something like
[blue][tt]
AlphaCode = Trim$(Str$(((Number \ 27) + 1 ))) & Chr$((Number MOD 26) + 64)
[/tt][/blue]?

 

[SkipVought]

impulse,

I entered this formula in A1 on an Excel spreadsheet and copied down
[tt]
=INT((ROW()-1)/26)+MOD(ROW()-1,26)+1&CHAR(MOD(ROW()-1,26)+65)
[/tt]
So if you want to use code rather than a spreadsheet, substitute the Numeric Value to convert for ROW()

ConVal = Int((NumVal - 1) / 26) + (NumVal - 1) Mod 26 + 1 & Chr((NumVal - 1) Mod 26 + 65)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[impulse24]

thanks for the help golom..it almost works, but when I have 26 it produces 1@, and 234 produces 9@, so it looks like anytime the value should be Z it is using a @.
Skip, i tried yours but the result for 26 was 26Z instead of 1Z, not sure where it went wrong

 

[SkipVought]

duh! sorry
[tt]
=INT((ROW()-1)/26)+1&CHAR(MOD(ROW()-1,26)+65)
[/tt]

ConVal = Int((NumVal - 1) / 26)  & Chr((NumVal - 1) Mod 26 + 65)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Golom]

OK. I guess it needs to be
[blue][tt]
AlphaCode = Trim$(Str$(((Number \ 27) + 1 ))) & _
Chr$(IIF(Number MOD 26=0,1, Number MOD 26) + 64)
[/tt][/blue]

 

[bmdb]

SkipVought, you almost had it the second time....try this...

ConVal = Int((NumVal + 1) / 26) & Chr((NumVal - 1) Mod 26 + 65)

 

[SkipVought]

Yea, I missed the + 1 -- and NOTICE the place...

ConVal = Int((NumVal - 1) / 26) + 1 & Chr((NumVal - 1) Mod 26 + 65)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bmdb]

Yeah, I just figured that out....DOH