Forum Discussion
Split cell and Calculate formula
You want to look the price of each item up in another sheet, then multiply by the quantity (after the /) and then multiply by another quantity in the adjacent cell (2600)?
Assuming your example is in Sheet1!A1:C1. Assuming your price list is at Sheet2!A1:B3 (description in Col A and price in Col B). And, assuming the individual items in cell C1 are separated by Alt+Enter (CHAR(10)), then
Select Sheet1, cell D1, then click name manager and enter:
Name = Eval
Scope= Select your worksheet
RefersTo= EVALUATE(TEXTJOIN("+",TRUE,SUBSTITUTE(SUBSTITUTE(FILTERXML("<L><I>"&SUBSTITUTE(Sheet1!C1,CHAR(10),"</I><I>")&"</I></L>","//I"),Sheet2!$A$1:$A$3,Sheet2!$B$1:$B$3),"/","*")))
Then, in cell D1 enter:
=B1*Eval
I also included my test workbook.
JMB17 , that's a great idea.
If translate your idea literally to avoid EVALUATE that will be
=LET(
value,B1,
text, C1,
tableText,Sheet2!$A$1:$A$3,
tableNumber, Sheet2!$B$1:$B$3,
splitLines, FILTERXML("<L><I>"&SUBSTITUTE(C1,CHAR(10),"</I><I>")&"</I></L>","//I"),
replaceTexts, SUBSTITUTE(splitLines,tableText,tableNumber),
lookupNumbers, LEFT(replaceTexts,SEARCH("/",replaceTexts)-1),
numbers, RIGHT(replaceTexts,LEN(replaceTexts)-SEARCH("/",replaceTexts)),
Result, SUM(lookupNumbers*numbers)*value,
Result)
If the table with the data will be like
when we may use
=LET(
value,B1,
text, C1,
splitLines, FILTERXML("<L><I>"&SUBSTITUTE(text,CHAR(10),"</I><I>")&"</I></L>","//I"),
getTexts, LEFT(splitLines,SEARCH("/",splitLines)-1),
getNumbers, INDEX(tblData[Value],XMATCH(getTexts,tblData[Name])),
numbers, RIGHT(splitLines,LEN(splitLines)-SEARCH("/",splitLines)),
Result, SUM(getNumbers*numbers)*value,
Result)