Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Feb 24, 2025
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 " , " , marked in Blue.

Thanks in Advance,

Br,

Anupam

excel
Formulas and Functions
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 24, 2025

    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.

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 24, 2025

    If you are happy with a 365 formula (rather than text to columns) you could use

    =REGEXEXTRACT(@data,"\(\-?\d+\,\s?\-?\d+\)",1)

    This does not use a separator at all but, rather, extracts any number pairs that match the format irrespective of what separates them.

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Feb 24, 2025

      PeterBartholomew1 , not to care about digits that could be

      =REGEXEXTRACT( @data, "(?xim) \(   (?>[^()] | (?R) )*   \)", 1)

      or

      =REGEXEXTRACT( @data, "(?xim) \(   (?:[^()] | (?R) )*\)",1 )

      but we have spills as result, not structured table.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 24, 2025

    My first thought was to replace all occurrences of "), " with "): " and then split by ": ", but PQ somehow wouldn't let me. Don't really understand why, as the parentheses and spaces are 'regular' characters.

    So, if you can somehow substitute "), " with "): " in the source file, PQ should handle it correctly. I tried by taking your data and use the SUBSTITUTE function in Excel. Then connected PQ to that table and could do the split.

     

    Edit: I wrote my earlier comments when working on a Mac and it wouldn't let me. Now trying on a PC and PQ lets me replace "), " with "): " and then split based on ": " without any issues.

     

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Feb 24, 2025

      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
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Feb 24, 2025

        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.

Resources