Syntax for 'Any Number'. Replace with "/". VBA

[killpaddy]

Hi,

I have a list of data with a mix of text, numbers and symbols in excel, such as:
'Quick Turn 15, 18, 20, 200
Dual Turn 20
8 Station, 8 Position
QT Nexus 150/200/250/300/350
SQT 200, 250
12 Station, 12 Position
QT Smart 150S / 300 / 350'

(note this is an example of what is contained in a single cell, not 7 consecutive cells)

I would like the ", " to be replaced with "/" between the numbers on the first line (e.g. '15/18/20/200')

however i dont want the ", " after 'station, ' to be replaced

I have this at the moment, but is there a shorter way, possibly using an 'any number' symbol.

Columns(1).Replace "0, ", "0/"
Columns(1).Replace "1, ", "1/"
Columns(1).Replace "2, ", "2/"
Columns(1).Replace "3, ", "3/"
Columns(1).Replace "4, ", "4/"
Columns(1).Replace "5, ", "5/"
Columns(1).Replace "6, ", "6/"
Columns(1).Replace "7, ", "7/"
Columns(1).Replace "8, ", "8/"
Columns(1).Replace "9, ", "9/"

 

[killpaddy]

Also, It may be useful to do this for 'any letter' aswell

Thank you

 

[Sandeep Warrier]

How about this?

You can try =ReplaceComma(A1,False) to replace any letter.

Function ReplaceComma(sInp As String, Optional RepNum As Boolean = True) As String
    Dim Temp As String
    Dim Pattern As String
    
    ReplaceComma = sInp
    
    If RepNum Then
        Pattern = "((\d+),)+"
    Else
        Pattern = "([A-Za-z]+, )+"
    End If
    
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = Pattern
        If .test(sInp) Then
            Temp = .Replace(sInp, "$2/")
            ReplaceComma = Replace$(Temp, "/ ", "/")
        End If
    End With
End Function

Excel 2010
	A	B
1	Quick Turn 15, 18, 20, 200 Dual Turn 20 8 Station, 8 Position QT Nexus 150/200/250/300/350 SQT 200, 250 12 Station, 12 Position QT Smart 150S / 300 / 350	Quick Turn 15/18/20/200 Dual Turn 20 8 Station, 8 Position QT Nexus 150/200/250/300/350 SQT 200/250 12 Station, 12 Position QT Smart 150S /300 /350
Sheet1

Cell Formulas
Range	Formula
B1	=ReplaceComma(A1)