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.
Text to Number
Is there some sort of logic that I'm not seeing?
A = 3
B = ?
AA = 5
AB = ?
AAA = ?
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
(posted up as I'm off to bed here....10pm already)
I Have a couple of ideas :eureka: here can you post a workbook with about 20 rows of examples .
Col A = A,B,C,D,E,AA,SCH"
Col B = 5 , 4, ect so I know what you want A or B or C to be converted to?
Jack in the UK
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.
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?
Yes to the frist question. I didn't understand the second question. Thanks for the help.
OK, this is going to require VBA or you can mess about with your AutoCorrect, which I don't advise.
The VBA method:Code
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Count <> 1 Then Exit Sub Application.EnableEvents = False Select Case Target.Value Case "AA" Target.Value = 1 Case "A" Target.Value = 2 Case "B" Target.Value = 3 Case "C" Target.Value = 4 Case "D" Target.Value = 5 Case "E" Target.Value = 6 Case "SCL" Target.Value = 7 End Select Application.EnableEvents = True End Sub
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.
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.
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.
I can't run my macros unless I have this digital signture.
Can I make this macro run on just sheet 2 or sheet 3.
Here's the code to make it run only on sheets names "Sheet2" and "Sheet3":Code
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Count <> 1 Then Exit Sub Select Case Sh.Name Case "Sheet2", "Sheet3" Select Case Target.Value Case "AA" Target.Value = 1 Case "A" Target.Value = 2 Case "B" Target.Value = 3 Case "C" Target.Value = 4 Case "D" Target.Value = 5 Case "E" Target.Value = 6 Case "SCL" Target.Value = 7 End Select End Select End Sub
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.
Thank you very much it works great. Now I can finish my macro.
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?
...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:
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:Code
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim objTargetCell As Range For Each objTargetCell In Target Select Case Sh.Name Case "Sheet2", "Sheet3" Select Case objTargetCell.Value Case "AA" objTargetCell.Value = 1 Case "A" objTargetCell.Value = 2 Case "B" objTargetCell.Value = 3 Case "C" objTargetCell.Value = 4 Case "D" objTargetCell.Value = 5 Case "E" objTargetCell.Value = 6 Case "SCL" objTargetCell.Value = 7 End Select End Select Next End Sub
Don’t have an account yet? Register yourself now and be a part of our community!