Multiply the value mentioned post "'-" Sign

[Naresh Yadav]

Respected All,

I need a formula Help that can help me to Multiply the value mentioned post "'-" Sign in first Column (Separate Heading) with the Order Qty
I have atttached the excel file for your kind

Separate	Order Qty	Expected Result
1211332-2|1211088-2|1211007-3	2	1211332-4|1211088-4|1211007-6
1211332-4|1211088-4	3	1211332-12|1211088-12
6111085-1|6111089-1|6111077-1|6111081-1	5	6111085-1|6111089-1|6111077-1|6111081-1

 

[p45cal]

Power Query offering; update the value in the table on the left, then right-click the green table on the right and choose Refresh.
I don't think the last row of your expected results is correct.

Result

1211332-4|1211088-4|1211007-6

1211332-12|1211088-12

6111085-5|6111089-5|6111077-5|6111081-5

 

[Peter Bartholomew]

Why do the expected results for the third line finish "-1" rather than "-5"?

 

[Peter Bartholomew]

See file. It is 365.
I have changed the file to use a defined name for the Lambda function. The worksheet formula now reads

= MAP(separate, orderQty, AdjustedQtyλ)

where the function AdjustedQtyλ(sep, qty) is defined to be

= LET(
    table,    TEXTSPLIT(sep, "-","|"),
    quantity, qty,
    adjusted, TAKE(table,,1) & "-" & quantity*TAKE(table,,-1),
    TEXTJOIN("|",,adjusted)
  )

Not much use without 365 though!

 

[bosco_yip]

It is a simple formula to adopt Filterxml and Textjoin function if you have Excel 2019 or Excel 2021.

1] Define 2 names as per attachment.

2} In Result column, C2 formula copied down:

=TEXTJOIN("|",,Dig_1&Dig_2)