Forum Discussion
Solved
What delimiter shall I use?
Dear Experts, Could you please help me with what Delimiter shall I use in below case? So , I have data like in Column2, and want to split the column on the basis of " , " , mark...
That's as Patrick2788 suggested, or if you prefer with formula, practically the same could be
=TRIM( TEXTSPLIT(SUBSTITUTE( <line>, "), (", "),:(" ), ",:") )Above for single row, how to split texts in 2D you may find patterns, few of them. But again, that will be spill, not table.
Riny_van_Eekelen , strange, it works in my case
let
Source = Excel.CurrentWorkbook(){[Name="l1low_bfweight_Study"]}[Content],
SelectColumns = Table.SelectColumns(Source,{"Column1", "Column2"}),
SetDelimiter = Table.ReplaceValue(SelectColumns,"),","),:",Replacer.ReplaceText,{"Column2"}),
names = List.Transform(
{1..List.Max( List.Transform( SetDelimiter[Column2], (q) => List.Count( Text.Split(q, ":") ) ) ) },
(n) => "Pair" & Number.ToText(n, "00") ),
SplitColumns = Table.SplitColumn(
SetDelimiter, "Column2",
Splitter.SplitTextByDelimiter(",: ", QuoteStyle.Csv), names),
TrimTexts = Table.TransformColumns(
SplitColumns, List.Transform( names, (q) => {q, Text.Trim, type text} ) )
in
TrimTexts
Indeed, very odd. I edited my earlier response to explain I was using a Mac, and can't get it to work there. On a PC, no problems.