TextAfterDelimiter using another column as the text delimiter

[alannavavaldez]

Hello,

Sorry to ask, I had been trying and I was not able to do this... is there a way to split column A using column B as delimiter? I was thinking using TextAfterDelimiter but so far no luck. This is just a short list, but I have like 20 different delimiters in column B and it would help me to have something where I can reference that column.

Column A	Column B	Column C Result
material 1000 is not ok	material	1000 is not ok
batch 1100 not ok	batch	1100 not ok
product 1200 is ok	product	1200 is ok

[dufoq3]

Hi @alannavavaldez, for your sample data it is enough replace text:

Text.Trim(Text.Replace([Column A], [Column B], ""))

but if you have delimiter somewhere in the middle - this could help you:

Text.Combine(List.Select(List.Transform(Text.Split([Column A], [Column B]), Text.Trim), (x)=> x <> ""), " ")

[Vvelarde]

Another way is using this: 

Text.End([Column A], Text.Length([Column A])-Text.Length([Column B])-1), type text)

[lbendlin · Accepted solution]

It's not a proper split, as you usually sacrifice the delimiter symbol(s)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyk0sSS3KTMxRMDQwMFDILFbIyy9RyM9W0oHLKMXqRCslJZYkZygYAhUhFIDFwLIFRfkppcklCoZGEDPA0lBBpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B " = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Column C Result", each Text.Split([Column A],[#"Column B "]){1},type text)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[alannavavaldez]

thanks! it worked for me