replacing any alphabet with C, and number with 0 in an alphanumeClosed

Question

Hello, 

I have huge list of alphanumeric string in each row. And I want to replace any alphabet with C, and number with 0 in an alphanumeric. e.g I have ABCH986754321 I want to convert it to CCCC000000000 . So as you may see all alphabets with C and all numbers with 0.
One more example 45678XXH1234 would be 00000CCC0000.

I'll really appreciate if someone may provide any suggestions on this.

Thanks a lot.


Configuration: Windows 7 / Chrome 42.0.2311.90

TrowaD · Answer

Hi Raj,

Thanks for the nice request. 

Here is the code I got for you:
Sub RunMe()
Dim x, lRow As Integer
Dim MyValue As String

lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:A" & lRow)
    MyValue = cell.Value
    For x = 1 To Len(MyValue)
        If IsNumeric(Mid(MyValue, x, 1)) = True Then
            Mid(MyValue, x, 1) = 0
        Else
            Mid(MyValue, x, 1) = "C"
        End If
    Next x
    cell.Value = MyValue
Next cell
End Sub

Best regards,
Trowa

Replacing any alphabet with C, and number with 0 in an alphanume

1 response

Similar discussions

Subscribe To Our Newsletter!