Forum Discussion

Noxam_Sherei's avatar
Noxam_Sherei
Copper Contributor
Sep 02, 2020

Split cell and Calculate formula

Hi,   Little introduction: I try build complex and clear excel too calculate servals aspect of game economy, for beeter play and learn much more about excel. Including all that causes me a problem...
excel
JMB17's avatar
JMB17
Bronze Contributor
Oct 10, 2020

Noxam_Sherei 

 

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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 10, 2020

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)

Resources