# Thread: Letters to Numbers (2003)

1. ## Letters to Numbers (2003)

I have a table, which comes from another source. It is indexed A,B,C,D etc., through to AA, AB, AC etc. I want to report these in numerical form. Can this be done?

2. ## Re: Letters to Numbers (2003)

Say that the field is named ID. You can use this expression in a query:

NumID: IIf(Len([ID])=1,Asc(UCase([ID]))-64,26*(Asc(UCase([ID]))-64)+Asc(Mid(UCase([ID]),2))-64)

This assumes that you have two letters at most.

3. ## Re: Letters to Numbers (2003)

The first 26 are A-Z and I'm getting -831 for the first number

4. ## Re: Letters to Numbers (2003)

Check whether you have copied the expression correctly.

5. ## Re: Letters to Numbers (2003)

Yes, I cut & pasted.

6. ## Re: Letters to Numbers (2003)

That means that the first 26 don't contain "A" - "Z" but " A" - " Z" (with a leading space). Try this then:

NumID: IIf(Left([ID],1)=" ",0,Asc(UCase([ID]))-64)*26+Asc(Mid(UCase([ID]),2,1))-64

or

NumID: IIf(Len(Trim([ID]))=1,Asc(UCase(Trim([ID])))-64,26*(Asc(UCase([ID]))-64)+Asc(Mid(UCase([ID]),2))-64)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•