Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Alpha Character to return 2 digit number.

  1. #1
    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. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  3. #3
    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. #4
    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. #5
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  6. #6
    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  8. #8
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Seperate 2 Digit Number Into 2 Single Digit Numbers
    By Triggman in forum EXCEL HELP
    Replies: 4
    Last Post: April 3rd, 2008, 12:59
  2. Return Number Before Alpha Character
    By thelarster in forum EXCEL HELP
    Replies: 3
    Last Post: February 12th, 2008, 07:44
  3. Extract String After First Alpha Character
    By EROEI in forum EXCEL HELP
    Replies: 3
    Last Post: July 27th, 2007, 05:11
  4. Replies: 5
    Last Post: November 26th, 2005, 02:07
  5. test first character in each cell to see if alpha
    By wbsmith in forum EXCEL HELP
    Replies: 4
    Last Post: March 15th, 2005, 00:36

Bookmarks

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