Insert Delimiters into Text String at Regular Intervals (PowerQuery)

[Anonymous]

QUESTION

Can anybody help me write a PowerQuery function that can add two different delimiters every 4 and 8 digits in a text string of variable length?

Click Here for SAMPLE DATA! 

EXAMPLE OF A SINGLE CELL'S FRUSTRATING CONTENTS

####-####,  ######## , --, and ####-####

SITUATION

I have a column of cells containing very messy lists of 8-digit numbers (random delimiters, spaces, etc).  The length of the strings variess tremendously (ie. one cell contains three 8-digit numbers while the next contains only one 8-digit number).

Using Text.Select in PowerQuery, I am able to pull just the numbers into a custom column.  The result (using the above example) looks like this:  ######################## (ie. three 8-digit numbers).  

I cannot figure out how to add delimiting text into the resultant text string (I get a "number is too big" error when converting to whole number).  The delimiters I am adding are:

1. A hyphen "-" every 4 digits.
2. A comma + space ", " every 8 digits.  

The resulting column would contain entries like below:

####-####, ####-####, ####-####

####-####

####-####, ####-####

Can anybody help me write a function that can add these two delimiters every 4 and 8 digits in a text string of variable length?  I know I can split the column every 8 positions to produce a number of new columns equal to (longest string / 😎 but then they need to be combined again with the delimiters.  Since I'm always adding new monthly reports to this list, I need something less manual.

Thanks!

Ryan

[mahoneypat]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1M7ewMDczNTE2MlSK1YlWQuHAVCjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NumberText = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.Transform(List.Split(Text.ToList([NumberText]), 8), each Text.Combine(List.FirstN(_,4) & {"-"}& List.LastN(_,4))), ", "), type text)
in
    #"Added Custom"

Pat

[Anonymous]

Pat,

Thanks for this - it's great.  Could you help me re-write it as a function?  Its goals would be to...

1. Select only #'s from a string of garbage
   (ie. = Table.AddColumn(Source, "Custom", each Text.Select([#"Grievance#"], {"0".."9"})))
2. Run your M-code to effectively sort the variable-length string into ####-####, ####-####

As a labour relations professional, I get this exact same garbage data in a number of sheets from the many hands involved in the LR process and would die for a canned query-function to treat these cases with.  Unfortunately, I'm still brand new with the concept of functions and it will take me days to figure out how your M-code works.  PS.  If you were able to highlight the part of your M-code that instructs the function to apply the "####-####, ####-####" format sequencing (ie. a "-" every 4 numbers and a ", " every 8 numbers) it would be a great kindness on your part for the sake of my learning.

As an aside, I wrote a solution last night (because it was driving me crazy) that was far less sophisticated than yours.  I used Text.Select action to make a custom column of just the grievance numbers, then used Text.PadEnd to standardize the string lengths to 50, then created a second custom column using nine Text.Insert actions to intermittently add "-"s and " "s every 4 characters.  To deal with the excess formatting at the end of strings containing fewer than five eight-digit numbers (####-####) I split that second custom column using the delimiter "    -" that would occur the first time the formatting was applied to blank space.  I deleted that jargon column, trimmed the column with my good data, and performed a ReplaceValue action that swapped the spaces between the numbers (####-#### ####-####) with ", ".  This produced the clean data and format (####-####, ####-####) I was seeking.  But it was a long walk to get there.  Just wanted you to know that I wasn't resting on my laurels waiting for somebody else to solve my problem!  #StillLearning #DownButNotOut

The issue is that it cheats a solution to treating variable-length strings by using Text.PadEnd - this ultimately constrains the solution to strings that contain fewer than 5 eight-digit numbers (which I cannot guarantee).  

Here's the updated file.  Sheet 1 contains the sample data while the CleanedAndFormatted sheet contains the 8-step Query (not sure if you can edit somebody else's queries!  I can only open it on my desktop Excel).  

Updated Example Spreadsheet Data and Homemade Query Solution 

In case you cannot see the Query I wrote in the file above, there's the Query Settings Panel and each step from "Added Custom" (#2) to "Replaced Value" (#8):

= Table.AddColumn(Source, "Custom", each Text.Select([#"Grievance#"], {"0".."9"}))

= Table.AddColumn(#"Added Custom", "CustomPad", each Text.PadEnd([Custom],50))

= Table.AddColumn(#"Added Custom2", "Custom.1", each Text.Insert(Text.Insert(Text.Insert(Text.Insert(Text.Insert(Text.Insert(Text.Insert(Text.Insert(Text.Insert([CustomPad],4,"-"),9," "),14,"-"),19," "),24,"-"),29," "),34,"-"),39," "),44,"-"))

= Table.SplitColumn(#"Added Custom1", "Custom.1", Splitter.SplitTextByEachDelimiter({"    -"}, QuoteStyle.Csv, false), {"Custom.1.1", "Custom.1.2"})

= Table.RemoveColumns(#"Split Column by Delimiter",{"Custom.1.2"})

= Table.TransformColumns(#"Removed Columns",{{"Custom.1.1", Text.Trim, type text}})

= Table.ReplaceValue(#"Trimmed Text"," ",", ",Replacer.ReplaceText,{"Custom.1.1"})

Thanks for the help!

Ryan