Search code examples
excelexcel-formulapowerquerym

Separate data in Excel with 3 types of delimiters - Space, hyphen and =>

I recently received an Excel file and I'm struggling to separate the commission column to make my work easier in the future. I have 3 different types of delimiters for the percentage - space, hyphen and =>.

This is my sample data:

supplier date commission
adeccoss 1/1/2023 intre 4-6 mil $ - 2% 6-10 mil $ - 3% >10 mil $ - 5%
gigel 5/7/2023 intre 3-5 mil $ => 3% 5-8 mil $ => 5% >8 mil $ => 7%
floris 4/4/2023 intre 0.5-2 mil $ 1% 2-3 mil $ 2% 3-5 mil $ 3.5% 5-6 mil $ 4.5% >6 mil $ 6%

I tried with textafter function ,texsplit functions using Power Query but I can't get the data in the desired output as you may see:

supplier date commision percent
adeccoss 1/1/2023 4-6 mil $ 2%
adeccoss 1/1/2023 6-10 mil $ 3%
adeccoss 1/1/2023 >10 mil $ 5%
gigel 5/7/2023 3-5 mil $ 3%
gigel 5/7/2023 5-8 mil $ 5%
gigel 5/7/2023 >8 mil $ 7%
floris 4/4/2023 0-2 mil $ 1%
floris 4/4/2023 2-3 mil $ 2%
floris 4/4/2023 3-5 mil $ 3.5%
floris 4/4/2023 5-6 mil $ 4.5%
floris 4/4/2023 >6 mil $ 6%

Can you please help with any solution using formulas in office 365 or Power Query?

Thank you in advance.

Solution

  • Not elegegant, but it works:

    =LET(res,
 WRAPROWS(
     TOCOL(
       TEXTSPLIT(
           TEXTAFTER(
               SUBSTITUTE(
                  SUBSTITUTE(
                     SUBSTITUTE(
                        SUBSTITUTE(
                           SUBSTITUTE(TRIM(C2:C4),"% ","%|"&A2:A4&"|"&B2:B4&"|"),
                           "$ -","$"),
                        "$ =>","$"),
                     "$","$|"),
                  "intre","|"&A2:A4&"|"&B2:B4&"|"),
              "|",SEQUENCE(,99)),
          "|"),
        2),
    4),
IFERROR(--res,res))

    enter image description here

    or using lambda for the repeating SUBSTITUTE:

    =LET(a,{"% ";"$ -";"$ =>";"$";"intre"},
     b,{"%|#|";"$";"$";"$|";"|#|"},
res,
WRAPROWS(
    TOCOL(
      TEXTSPLIT(
          TEXTAFTER(
              SUBSTITUTE(
                 REDUCE(C2:C4,SEQUENCE(ROWS(a)),
                 LAMBDA(x,y,
                        SUBSTITUTE(x,INDEX(a,y),
                                     INDEX(b,y)))),
                 "#",
                 A2:A4&"|"&B2:B4),
              "|",SEQUENCE(,99)),
          "|"),
        2),
      4),
IFERROR(--res,res))