Convert alpha characters to numbers

[stephfr]

Hi

I need to convert data present in some cells to a numeric value (ideally at input time or on cell exit). The range is fixed as it relates to a user entering the numbers on a french keyboard but forgetting to use caps lock. I don't want to format the cell to only allow numeric values because the user will kill me if he keeps getting an error message.....

I need to convert the following:
& - 1
é - 2
" - 3
' - 4
( - 5
§ - 6
è - 7
! - 8
ç - 9
à - 0

I tried using the SUBSTITUTE function that I found on another website but it didn't work

Thanks
Steph

 

[pgc01]

Hi Steph
Welcome to the board

Paste this code in the worksheet module.

It will perform the replacement when you exit a cell in column A after you change its value:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
Dim s As String
Dim j As Long
 
If Target.Column = 1 Then ' if the cell changed is in column A

    v = [{"&",1;"é",2;"""",3;"'",4;"(",5;"§",6;"è",7;"!",8;"ç",9;"à",0}]
    
    s = Target.Value
    For j = 1 To 10
        s = Replace(s, v(j, 1), v(j, 2))
    Next j
    
    Application.EnableEvents = False
    Range("A1") = s
    Application.EnableEvents = True
    
End If
End Sub

 

[stephfr]

Hi PGC
Wonderful!!!

Thank you very much this is perfect

Steph

 

[stephfr]

Hi PGC

It works perfectly (and you're my hero!!). The only thing is that I need to click back on the cell again in order for it to change. Is that possible?

Thanks
Steph

 

[Rick Rothstein]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
Dim s As String
Dim j As Long
 
If Target.Column = 1 Then ' if the cell changed is in column A
 
    v = [{"&",1;"é",2;"""",3;"'",4;"(",5;"§",6;"è",7;"!",8;"ç",9;"à",0}]
 
    s = Target.Value
    For j = 1 To 10
        s = Replace(s, v(j, 1), v(j, 2))
    Next j
 
    Application.EnableEvents = False
    [COLOR=darkred]Range("A1")[/COLOR] = s
    Application.EnableEvents = True
 
End If
End Sub

Did you really mean Range("A1") or should that be Target instead?

 

[stephfr]

Hi Rick

I changed "A1" to

Range(Target.Address) = s



Steph

 

[Scott Huish]

You could just use:

Target=s

 

[Rick Rothstein]

Here is another Change event procedure that you can consider using...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim s As String, j As Long, Cell As Range
  For Each Cell In Target
   If Cell.Column = 1 Then
     s = Cell.Value
     For j = 1 To Len(s)
        If Mid(s, j, 1) Like "[à&é""'(§è!ç]" Then Mid(s, j) = InStr("à&é""'(§è!ç", Mid(s, j, 1)) - 1
     Next j
     Application.EnableEvents = False
     Cell = s
     Application.EnableEvents = True
   End If
  Next
End Sub

 

[Scott Huish]

Here's another version:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, e, x As Long
Set d = Intersect(Target, Range("A:A"))
If d is Nothing Then Exit Sub
e = Array("à", "&", "é", """", "'", "(", "§", "è", "!", "ç")
Application.EnableEvents = False
    For Each c In d
        For x = 0 To 9
            c = Replace(c, e(x), x)
        Next
    Next
Application.EnableEvents = True
End Sub

 

[pgc01]

Did you really mean Range("A1") or should that be Target instead?

Oops!