Easy way to convert A to 1, B to 2... Z to 26

[johnc83]

Hi,

I am in need of a function which needs to take a letter and convert it to it's equivalent number.

My aim is to take a set of letters (i.e ABCDEXYZ) and return the numbers 1, 2, 3, 4, 5, 24, 25, 26.

I don't really want to use a Select Case function because it seems a bit too long winded and I was hoping there was a simpler solution.

Thanks

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[SgtJarrow]

Try

Asc("A") - 64

The Ascii value for a capital A is 65, so Asc("A") - 64 is 1, Asc("B") - 64 is 2, and so on.

if you are also allowing lowers case letters and need to convert those as well:

Asc("a") - 96

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[johnc83]

Excellent, just what I was looking for.

Thanks a lot Robert, really appreciate it.

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[johnc83]

Hi again, seems the function I was looking for is slightly more complicated than I thought...

A=10, B=12, C=13, D=14...

11, 22 & 33 are skipped because they are multiples of the modulus ( ? )

so I need a way to convert it with this new criteria. Sorry if it looks like I'm being lazy but this is way over my head and I wouldn't know where to start!

Thanks

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[EBGreen]

Asc("A") - 54


[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[johnc83]

Hi EBGreen, that works if the letter is A but if applied to B it would result in 11 which is incorrect because that needs to be 12...

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[macleod1021]

Are 11, 22 & 33 the only ones skipped (does it keep going with all numbers divisible by 11)?

In either case, you could use a if...then to weed out the incorrect items.

something like

offset = 0

x = Asc("B") - 54

if (x/11) then
offset++
end if

x = x + offset


im in ur stakz, overflowin ur heapz!

 

[EBGreen]

Aaah, I see. Well then you are looking at two possibilities. A lookup or and algorithm.

The lookup could in the form of structured data such as a dictionary, or in structured logic such as a switch construct.

To do it algorithmically you would need to use Asc(foo) then add the integer part of Asc(foo)/11.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Sorwen]

A=10, B=12, C=13, D=14...

11, 22 & 33 are skipped because they are multiples of the modulus ( ? )

This comment doesn't quite make sense. If you could explain more about what you are trying to do or show some code then maybe we could help. You already are trying to do something different from what you said because you wanted A=1. It isn't like that is a big problem, but it makes it hard to help.



-I hate Microsoft!
-Forever and always forward.

 

[johnc83]

Hi macloed, that looks something like I need. I think it would work for B(12), but then when it came to calculate C, because it is NOT a divisible 11 number it work leave that as it is (12). This is the full list of combinations:

A=10 B=12 C=13 D=14 E=15 F=16 G=17 H=18 I=19 J=20
K=21 L=23 M=24 N=25 O=26 P=27 Q=28 R=29 S=30 T=31
U=32 V=34 W=35 X=36 Y=37 Z=38

Hope that makes sense.
Thanks

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[macleod1021]

Yes. Now...whether you use an algorithm or a dictionary would depend on your implementation. If you're only using A-Z all upper case, then a dictionary wouldn't be that much of a memory concern. But if you started to use the entire unicode set, the 8k on my TRS-80 wouldn't like it

im in ur stakz, overflowin ur heapz!

 

[johnc83]

Hi Sorwen, apologies for the confusion. Here is exactly what I am trying to achieve:

Say we have a product number PDQU120043. This needs a check digit on the end so that we know we have entered the full number correctly. The check digit in this instance is 5 so the full product number is PDQU1200435.

Using the following algorithm, I need to confirm that the first 10 alphanumerics match the last number. If someone typed in PDQU1200436 then it would flag it up saying "Please check product number"

So this is how it is calculated..

1. Convert the 10 numbers/letters into letters so using the chart above the full number would be
27 14 28 32 1 2 0 0 4 3

2. Mutliply each one of those by the following numbers..
1 2 4 8 16 32 64 128 256 512

3. And then add them together
27 + 28 + 112 + 256 + 16 + 32 + 64 + 0 + 0 + 1024 + 1536
(Total = 3063)

4. Divide that by 11 (3063 / 11) = 278.45

5. Remove decimals and multiply by 11. (278 * 11) = 3058

6. Subtract the result from 5 (3058) from 3 (3063)

Result = 5 which is the correct value.

Hope that clears everything up.

Thanks

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Sorwen]

Ah, ok.

-I hate Microsoft!
-Forever and always forward.

 

[gmmastros]

I suggest you create an array to do the lookups. This array would contain your lookup values. Arrays begin at 0, so you could have....

Dim arLookup(25) As Integer

arLookup(0) = 10
arLookup(1) = 12
arLookup(2) = 13
etc....

Then, when you want to get the lookup value....

arLookup(Asc("A")-65)

Of course, you will need to make sure that you don't accidentally try to lookup a value that is not in the range of acceptable lookup values.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[johnc83]

Doesn't make any sense, but works perfectly! I'll try to get my head round it at some point...

Cheers, job done!

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express