Separate each 11 digits in one line per occurrence

YasserKhalil
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Separate each 11 digits in one line per occurrence

Post by 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
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Re: Separate each 11 digits in one line per occurrence

Post by 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

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Separate each 11 digits in one line per occurrence

Post by HansV »

That is quick!
Best wishes,
Hans