Text to Number

  • I want to convert text like (A) to 3 or (AA) to 5. Never more than three letters at a time in the cell. Thanks for the help.

  • in addition to Denis's question, what do you mean by "convert" ?

    would you like your "AA" to turn into a 5 when it's inputted ?

    maybe you'd like "AA" to be entered in a cell and have the cell next to it show the value 5 ?

    or do you already have a list of codes and you want to replace them (by hook or by crook) with your list of numbers ?

    as much info as you can give all helps towards providing a solution..... we don't know if you have just 5 codes (see Denis's example above) or if you have a potential 27x27x27 = 19,683 codes - each example maybe driving a different solution based on rescources / speed and complexity


  • received via email :

    Thanks for getting back to me Chris. Your right about not enough
    info. I want the cell to convert any text that is imported into
    it to a number. There is only ten letters that would be
    converted, but three is the max at any one time, that would be
    in the cell. Here is an example of the letters
    "A,B,C,D,E,AA,SCH" I hope this helps. Thank you for looking into
    my problem.

    (posted up as I'm off to bed here....10pm already)

  • Frist off I would like to thank eveyone for the help that your giving me. All of the convertions must be done in one cell.
    So if any of these letters is imported in to
    the cell it is converted to a number.
    Thanks Charlie

  • So what your are saying, for example, is that if I type in an "A" in Cell A1, you want this automatically to change to "2"?

    Is there a particular range that these values will be entered into or just if any cell in the workbook contains any of these codes?

  • OK, this is going to require VBA or you can mess about with your AutoCorrect, which I don't advise.

    The VBA method:

    To use this:

    1. open your workbook
    2. Hit ALT+F11
    3. Double click on "ThisWorkbook" in the Project Explorer
    4. Paste the code into the Window that appears.

    You can start typing "A", "AA", "SCL" all over your workbook and they will be replaced by the appropriate numeric value.

    The second question I was asking you was, do you want to limit what cells you can type A, B, C, D, E, AA and SCL into and have them converted? i.e. only change values on Sheet1 in Column A?

    The way it is set up just now is that if "A" is typed into any cell in the workbook, it will be converted to "2".

  • Now I understand your second question, and yes I would like to limit the number of cells that this happens in. How would I do that.
    Thanks Charlie

  • What range do you want to limit this function to? It will be easier for me to write this explicitly rather than give you a generic example.

  • This set up works great it is excatly what I want to do. Thank you so much. I don't know what cells I'll be using. I'm at work and my spreadsheet is at home. Do you know anything about Self Certification for macros? Ive done it before but I'm having a heck of a time remembering how I did it.
    Thanks Charlie

  • Oh you mean Digital Signatures and all that malarky? I've never really used them I'm afraid.

    Actually, the code I gave you, it's probably best if you remove the two statements that say

    Application.EnableEvents = False (and the second one = True

    They're just more hassle than they're worth.

  • Here's the code to make it run only on sheets names "Sheet2" and "Sheet3":

    We regards to the digital signature issue. I'm assuming that your security is set to "high" which automatically disables workbooks with macros from anyone but a Trusted Source.

  • Denis,
    How come I can't paste my letters into sheet 3 and have them convert? It only works if I type it in. Can this be fixed?
    Thanks Charlie

  • ...because you didn't ask for that. The code would have to be written very differently to enable that. The first line of the code:

    If Target.Count <> 1 Then Exit Sub

    Stops the rest of the code from executing if more than one cell is selected. Primarily, this is used to stop an error occurring when the user deletes a lot of cell contents at once.

  • Here's some code that should do as you ask:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!