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

Closed
Raj - Apr 26, 2015 at 11:01 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Apr 30, 2015 at 11:37 AM
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.


1 reply

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Apr 30, 2015 at 11:37 AM
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
0