Replace Alpha with Numeric Character

[Gos-C]

Hi all,

Each cells in a column E range contains a 15-character numeric or alphanumeric string - e.g., 000000123456789, 000000000F16520, 000000TEST12345 and 000000000123XY9. I have to add a number to the cell value and then multiply it by another number. Therefore, I need to replace all alpha with numeric characters. I am having difficulty writing the code. Here is what I have:

Sub ReplaceAlpha()
Dim rCell As Range
Dim lLR As Long
lLR = Cells(Rows.Count, "E").End(xlUp).Row
For Each rCell In Range("E2:E" & lLR)
rCell = Search(rCell, "a", 1)
rCell = Search(rCell, "b", 2)
rCell = Search(rCell, "c", 3)
rCell = Search(rCell, "d", 4)
rCell = Search(rCell, "e", 5)
rCell = Search(rCell, "f", 6)
rCell = Search(rCell, "g", 7)
rCell = Search(rCell, "h", 8)
rCell = Search(rCell, "i", 9)
rCell = Search(rCell, "j", 1)
rCell = Search(rCell, "k", 2)
rCell = Search(rCell, "l", 3)
rCell = Search(rCell, "m", 4)
rCell = Search(rCell, "n", 5)
rCell = Search(rCell, "o", 6)
rCell = Search(rCell, "p", 7)
rCell = Search(rCell, "q", 8)
rCell = Search(rCell, "r", 9)
rCell = Search(rCell, "s", 1)
rCell = Search(rCell, "t", 2)
rCell = Search(rCell, "u", 3)
rCell = Search(rCell, "v", 4)
rCell = Search(rCell, "w", 5)
rCell = Search(rCell, "x", 6)
rCell = Search(rCell, "y", 7)
rCell = Search(rCell, "z", 8)
End Sub

Can someone help me, please.

Thank you,
Gos-C

 

[Gos-C]

Hi mikerickson,

Thank you very much for the explanation.

I modified the macro as follows:

Sub test()
    Dim oneCell As Range
    For Each oneCell In Range("E2", Range("E" & Rows.Count).End(xlUp))
        oneCell.Offset(0, 1).Value = (AlphaToNum(CStr(oneCell.Value)) + 678678) * 12
    Next oneCell
End Sub

How do I modify it further to format it back to a 15-character string?

Thanks,
Gos-C

 

[mikerickson]

Use something like Format(aValue, "000000000000000")

 

[Gos-C]

Hi mikerickson,

I can't get it to work. Can you please give me the exact code.

Thank you,
Gos-C