Clear text of specific characters in a celll with VBA

Question

I'm looking for some help please with some VBA.

I have the next table

header1
000Model Test0Model Val00User0
Perman000User0Model Name000000
000Perman00000000000000000000Name

So I need to replace all Ceros with only one "," like this

header1
,Model Test,Model Val,User,
Perman,User,Model Name,
,Perman,Name

Is there a combination of formulas to do this? or with code in VBA?

Answer 1

Please, try the next function:

Function replace0(x As String) As String
    Dim matches As Object, mch As Object, arr, k As Long
    ReDim arr(Len(x))
     With CreateObject("VbScript.regexp")
        Pattern = "[0]{1,30}"
        .Global = True
        If .test(x) Then
            replace0 = .replace(x, ",")
        End If
     End With
End Function

It can be tested using:

Sub replaceAllzeroByComma()
   Dim x As String
   x = "000Perman00000000000000000000Name"
   'x = "000Model Test0Model Val00User0"
   'x = "Perman000User0Model Name000000"
   Debug.Print replace0(x)
End Sub

Uncheck the checked lines, one at a time and see the result in Immediate Window (Ctrl + G, being in VBE)