Macro to replace digits with 9, letters (upper or lower case) with L, other with _

[ellison]

Hi, we are trying to find a macro which would replace the alphanumeric contents of each cell with:
-letters with "L"
-digits with "9"
-any other remaining characters with "_"

Example:

XC/73:54-15PC 44C becomes LL_99_99_99LL_99L

<tbody>
</tbody>

Best
NEil

 

[Joe4]

I suspect someone may be able to come up with something more efficient, but here is a User Defined Function (UDF) that will do the conversion:

Function ConvertEntry(myEntry As String) As String

    Dim i As Long
    Dim a As Integer
    Dim myString As String

'   Exit if empty
    If Len(myEntry) = 0 Then Exit Function
    
'   Loop through all characters
    For i = 1 To Len(myEntry)
'       First check for numbers
        If IsNumeric(Mid(myEntry, i, 1)) Then
            myString = myString & "9"
        Else
'           Check for letters
            a = Asc(Mid(myEntry, i, 1))
            If ((a >= 65) And (a <= 90)) Or ((a >= 97) And (a <= 122)) Then
                myString = myString & "L"
            Else
'               Default rest to underscore
                myString = myString & "_"
            End If
        End If
    Next i
    
    ConvertEntry = myString

End Function

So after placing that in VBA, you can use it like any other function right on the worksheet, or in VBA, i.e.

Worksheet formula

=ConvertEntry(A1)

VBA

Dim cell As Range
For Each cell In Range("A1:A10")
    cell.Value = ConvertEntry(cell.Value)
Next cell

 

[Rick Rothstein]

I suspect someone may be able to come up with something more efficient, but here is a User Defined Function (UDF) that will do the conversion:

Function ConvertEntry(myEntry As String) As String

    Dim i As Long
    Dim a As Integer
    Dim myString As String

'   Exit if empty
    If Len(myEntry) = 0 Then Exit Function
    
'   Loop through all characters
    For i = 1 To Len(myEntry)
'       First check for numbers
        If IsNumeric(Mid(myEntry, i, 1)) Then
            myString = myString & "9"
        Else
'           Check for letters
            a = Asc(Mid(myEntry, i, 1))
            If ((a >= 65) And (a <= 90)) Or ((a >= 97) And (a <= 122)) Then
                myString = myString & "L"
            Else
'               Default rest to underscore
                myString = myString & "_"
            End If
        End If
    Next i
    
    ConvertEntry = myString

End Function

Here is another way to write your UDF...

Function ConvertEntry(ByVal myEntry As String) As String
  Dim X As Long
  For X = 1 To Len(myEntry)
    If Mid(myEntry, X) Like "[A-Za-z]*" Then
      Mid(myEntry, X) = "L"
    ElseIf Mid(myEntry, X) Like "#*" Then
      Mid(myEntry, X) = "9"
    Else
      Mid(myEntry, X) = "_"
    End If
  Next
  ConvertEntry = myEntry
End Function