Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Alpha Character to return 2 digit number.

1. I agreed to these rules
Join Date
14th August 2004
Posts
4

## Alpha Character to return 2 digit number.

When programming cash register department names, some manufactures require a series of two digit number keyboard entries for each letter used in a department name. Each department name could be as long as 8 digits with as many as 60 departments.

The programming manual will have a Alpha to number "key-grid" and the programmer has to translate by hand, then enter the 2 digit sequence into the Cash register i.e. to program CIGS / C always returns 23, I always returns 17, G always returns A3, S always returns 7G.
A1 A2 A3 A4 A5

1 CIGS (displayed) 23, 17 ,A3, 7G

I would like to simplify the process by entering each letter of the department name into a cell and have XL return a sequence of 2 digits.

So, can someone suggest a method or formula that will cause a pre-determined, 2 digit number to be displayed in an adjacet cell when a letter from A - Z is entered in a cell?

Thank-you

Excel Video Tutorials / Excel Dashboards Reports

2. If you don't care what the numbers are, use the CODE function:

=CODE(A1)

This will return the teo-digit code number of the first character in cell A1.

If you want to put the whole word in one cell (e.g. A2), then something like the following entered into B2 and dragged across to the right will work.

=IF(COLUMN()<=LEN(\$A2)+1,CODE(MID(\$A2,COLUMN()-1,1)),"")

See the attached.

3. I agreed to these rules
Join Date
14th August 2004
Posts
4
Wow, thomach! That was a quick response...Thank you!

I'll check out your suggestions and post the results.

TBone.

Excel Video Tutorials / Excel Dashboards Reports

4. I agreed to these rules
Join Date
14th August 2004
Posts
4
Thomach,

The format is exactly right! At column A1 enter the variable department name, and boom the corresponding digits are automatically translated, well done Thomach!

However, the two digits returned need to be the letter codes given by the Cash Register manufacturer in their Alpha to number Key-grid, not XL letter "code".

All I need now is to subsititute the computer generated "CODE" number for a letter and tell the formula to search and display a "GIVEN" 2 digit code of each letter A through Z entered in column A1.

We're so close to getting this it's scary!

Excel Video Tutorials / Excel Dashboards Reports

5. Substituting the VLOOKUP function as follows may do it.

=IF(COLUMN()<=LEN(\$A3)+1,VLOOKUP(MID(\$A3,COLUMN()-1,1),\$F\$7:\$G\$20,2,FALSE),"")

This requires you to create the lookup table. ALso, please note that VLOOKUP does not (repeat not) distinguish between upper and lower case -- they both return the same result. If this is ok, then you are all set. If not, then we need to use another function (I think MATCH cares about case).

See the updated attachment.

6. I agreed to these rules
Join Date
14th August 2004
Posts
4
Hi Tom:

Thanks so much for your insight, I've learned a couple of things from you!

Well, such as it is, here's the "translator", as simple as this program appears to be it may save much frustration having to program Cash Registers Department Names by manually following a grid and painfully writing down each sequence of numbers for letters. Now almost anyone can be given a list of department names, enter and print out a list quickly for the programmer!

I tweaked the Vlookup table by moving it and figured out how to change the corresponding \$strings.

One more thing, as for "spaces" e.g. OIL CHANGE, how is a space recognized to return or display the corresponding two digits?

I'll try not bother you on this any longer, as we both might miss the long weekend!

TBone

Excel Video Tutorials / Excel Dashboards Reports

7. Hi,

A space is a character, so just add a space character into the first column of the lookup table. I did that in the following and also added a "-" character. Remember to expand the table reference in the formula to match the new table range.

I'll attach this version now, but may play a bit and post another version shortly that has a few extra "features" for you. First I'll see if I can get what I'm thinking of to work.

8. The attached has the same function as the previous, but looks a bit different.

1. I moved the LOOKUP table to a different sheet so that a user was less likely to corrupt/change it accidnetly by overtyping or deleting information. I then also hide the sheet. You can unhide it from the toolbar:

FORMAT > SHEET > UNHIDE > select it from the dropdown list -- "LOOKUP" is what I think I named it.

2. I assigned a NAMED DYNAMIC RANGE to the LOOKUP table ("MyRange") which will automatically adjust if you add more rows/characters to the table (as long as you do not create an empty row within the table -- then it will not count accurately). The NAME "MyRange" is now used in the formulas so that if you add data to the table the formuals will automatically update and not need to be updated manually.

3. I formatted the cells with the lookup formula in them (light yellow with dotted-line edges) to indicate which cells are usable. You can drag the formula to more cells if your entries get longer.

OzGrid has lots of good data on DYNAMIC NAMED RANGES. They are very useful. You create them from INSERT > NAME > DEFINE. Usually an OFFSET function is used to calcualte and return a range value. This is what I did if you look at the formula in the NAME box.

Hope this isn't too much. But it sounds like you want to learn new tricks, and these a a couple of useful ones IMO.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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