Split column by delimiter with a character immediately after it

[Tomhayw]

Hi there,

I have this (dummy) string of text that I am trying to split into columns:

This,piece, of,text,needs,to,be,split

The delimiter I want to split by has a character immediately before and after it.

This

piece, of

text

needs

to

be

split

 The one that I don't want to split as a delimiter has a space immediately after it.

How do I go about this?

Thanks,

Tom

[AlexisOlson]

A fancier way to do this would be to use a character transition splitter. This will keep the commas though, so you'd probably want to trim those off.

Full sample query you can paste into the Advanced Editor of a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnILNYpyExNTtVRyE/TKUmtKNHJS01NKdYpyddJStUpLsjJLFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]), 
  #"Split Column by Character Transition" = Table.SplitColumn(
    Source, 
    "Text", 
    Splitter.SplitTextByCharacterTransition({","}, each _ <> " ")
  ), 
  #"Trimmed Text" = Table.TransformColumns(
    #"Split Column by Character Transition", 
    {}, 
    each Text.TrimEnd(_, ",")
  )
in
  #"Trimmed Text"

[watkinnc · Accepted solution]

You can use Replace values, and replace ", " with just " " or something that sticks out like "*', and then split by the comma ",".

--Nate

[Tomhayw]

Thank you,

I was too busy trying to overcomplicate it I forgot about the easy way around!