Add field delimiters to text string on/after a field with a specific pattern (e.g., "AAA111")

Question

I have a list of descriptions, followed by a short-code for the item (that is always 3 alphas followed by 3 numeric digits), followed by a series of digits representing occurrences. What would be the ideal way to tab/comma delimit after the description, starting with the short code?

Ex:

LANDLORD ACCESS LSO012 1 0 10 10 11
AGGRESSIVE DOG LSO016 0 0 5 5 5
ABANDONMENT OF SERVICE PLANNED LSO022 0 0 8 8 8

Ideally would be:

LANDLORD ACCESS, LSO012, 1, 0, 10, 10, 11 (new row/return)
AGGRESSIVE DOG, LSO016, 0, 0, 5, 5, 5 (new row/return)
ABANDONMENT OF SERVICE PLANNED, LSO022, 0, 0, 8, 8, 8 (new row/return)

Did you see [this question](http://stackoverflow.com/q/362444/2144390)? — Gord Thompson, Apr 07 '15 at 22:20
Are you looking specifically for a way to create a Notepad++ macro or plugin, or do you just want to process the input file to create the desired output (not necessarily in Notepad++)? — Gord Thompson, Apr 08 '15 at 19:03
Thanks for the replies. Writing a Macro would be no good as the source file varies from day to day. I ended up getting some assistance in VBA that helped: http://pastie.org/10078998 — Mike, Apr 09 '15 at 14:26

Gord Thompson · Answer 1 · 2015-04-09T15:25:30.553

(This is the approach that I came up with earlier. I held off on posting it since it was unclear whether you were specifically looking for a Notepad++ implementation. It is very similar to the solution that you found elsewhere.)

Option Compare Database
Option Explicit

Sub regexDemo()
    ' VBA project reference required:
    ' Microsoft VBScript Regular Expressions 5.5
    Dim strIn As String, strOut As String
    Dim rgx As New RegExp, rgxMatches As MatchCollection, rgxMatch As Match, _
            rgxSubMatches As SubMatches
    Dim part1 As String, part2 As String, part3 As String
    Open "C:\__tmp\regexDemoIn.txt" For Input As #1
    Open "C:\__tmp\regexDemoOut.csv" For Output As #2
    Do While Not EOF(1)
        Line Input #1, strIn

        ' set RegExp pattern to split line into 3 parts
        ' e.g. "^(WHAT EVER)( ABC123 )(MORE STUFF)$"
        rgx.pattern = "^(.*)( \w{3}\d{3} )(.*)$"
        rgx.Global = False
        Set rgxMatches = rgx.Execute(strIn)
        Set rgxMatch = rgxMatches.Item(0)
        Set rgxSubMatches = rgxMatch.SubMatches
        part1 = rgxSubMatches.Item(0)
        part2 = rgxSubMatches.Item(1)
        part3 = rgxSubMatches.Item(2)
        Set rgxSubMatches = Nothing
        Set rgxMatch = Nothing
        Set rgxMatches = Nothing

        ' now comma-separate trailing (part3) items
        rgx.pattern = "\s+"
        rgx.Global = True
        part3 = rgx.Replace(part3, ",")

        strOut = part1 & "," & Trim(part2) & "," & part3
        Print #2, strOut
    Loop
    Set rgx = Nothing
    Close #2
    Close #1
End Sub

Add field delimiters to text string on/after a field with a specific pattern (e.g., "AAA111")

1 Answers1