Remove text or numbers or special Characters in an alphanumeric string using VBA

Remove alphabets and special Characters in an alphanumeric string using VBA

As shown in below image let us assumes we have Range A2 to A6 containing alphanumeric strings and our desired output is shown from B2:B6. VBA code to achieve this results are mentioned below.

Code:

 Sub Remove_Alphabets_SpecialChar_Test()  
   Dim RegX As Object  
   Dim Rng As Range  
   Set RegX = CreateObject("vbscript.regexp")  
   With RegX  
     .Global = True  
     .Pattern = "[^-0-9]"  
   End With  
   For Each Rng In Range("A2:A6")  
     Rng.Offset(, 1) = RegX.Replace(Rng, "")  
   Next Rng  
 End Sub  

Remove alphabets and special Characters from alphanumeric string and retain decimal number using VBA

Well sometimes you may want to keep decimal numbers in desired output. Then you could use the below code.

Code:

 Sub Remove_Alphabets_SpecialChar_RetainDecimalNumber_Test()  
   Dim RegX As Object  
   Dim Rng As Range  
   Set RegX = CreateObject("vbscript.regexp")  
   With RegX  
     .Global = True  
     .Pattern = "[^-0-9-.]"  
   End With  
   For Each Rng In Range("A2:A6")  
     Rng.Offset(, 1) = RegX.Replace(Rng, "")  
   Next Rng  
 End Sub  

Remove numbers and special Characters from alphanumeric string using VBA

To retain alphabets and delete the numbers you can use following code.

Code:

 Sub Remove_Numbers_SpecialChar_Test()  
   Dim RegX As Object  
   Dim Rng As Range  
   Set RegX = CreateObject("vbscript.regexp")  
   With RegX  
     .Global = True  
     .Pattern = "[^-A-Z-a-z]"  
   End With  
   For Each Rng In Range("A2:A6")  
     Rng.Offset(, 1) = RegX.Replace(Rng, "")  
   Next Rng  
 End Sub