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

:cool:

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.
Gurnney

(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?

Knidest regards

Jack in the UK

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:

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".

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.

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":

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.

...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:

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