Separate each 11 digits in one line per occurrence

[YasserKhalil]

Hello everyone
I have some content of digits in cell and I need udf that separates all the numbers to have each number (of 11 digits) in one line. so I need to separate the numbers by line break
this udf is a try to do that but I got only the first occurrence

Code: Select all

Function FormatWithLineBreaks(ByVal inputString As String) As String
    Dim regex As Object
    Dim matches As Object
    Dim match As Object
    Dim formattedString As String
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "\d{11}"
    Set matches = regex.Execute(inputString)
    For Each match In matches
        formattedString = formattedString & match & vbCrLf
    Next match
    If Len(formattedString) > 0 Then
        formattedString = Left(formattedString, Len(formattedString) - 1)
    End If
    FormatWithLineBreaks = formattedString
End Function

example: 0101356517801095519905 needed to be like that
01013565178
01095519905

[YasserKhalil]

Solved

Code: Select all

Function FormatWithLineBreaks(ByVal inputString As String) As String
    Dim regex As Object, matches As Object, match As Object, s As String
    Set regex = CreateObject("VBScript.RegExp")
    regex.Global = True
    regex.Pattern = "\d{11}"
    Set matches = regex.Execute(inputString)
    For Each match In matches
        s = s & match & vbLf
    Next match
    If Len(s) > 0 Then s = Left(s, Len(s) - 1)
    FormatWithLineBreaks = s
End Function

[HansV]

That is quick!