New Contributor

# 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 active counting of production cubes

For now I use PowerQuery to collect acctual data from web, mainly buy, sell prices and other.

Next i have table with all recipe to construct various things. This recipe i write to one cell, and in second Power query i split this cell to rows and columns. Calculate this and send me information about cost and in thrird query group this prices.

This work good but is very unpleasand  to use.

Problem:

If is possibile to split cell using only excel formulas and calculate this?

For now I was think about  using LET formula to split cell, calculate materials and multiply by  price finded buy Index(Match), but I don't know how split cell to use in Excel formulas formula

Example:

 Gravimetric Sensor Cluster 2600 Titanium Carbide/22Nanotransistors/1Hypersynaptic Fibers/2

create formula who work somelike like this (write pseudocode)

SPLITFORMULA( cell to array where you calculate like example below:
SUM(INDEX(price;match(Titanium Carbide from other sheet)*22*2600 +
INDEX(price;match(Nanotransistors from other sheet)*1*2600 +
INDEX(price;match(Hypersynaptic Fibers from other sheet)*2*2600)

If you see something what I can use?

I slowly think about type new formula in VBA.

Best Regards,

Noxam

7 Replies

# Re: Split cell and Calculate formula

Better share the File with us, and let us examine the data to fix the issue. Seems you are trying to SUM value across workbook !!

# Re: Split cell and Calculate formula

Noxam, so far there is no TEXTSPLIT() formula in Excel. You may use FILTERXML() to split the text, but I don't think everything could be done in one cell, even with combining everything by LET().

First you shall split text lines, after that each line split on text and number which gives array in array; after that make some aggregation using arrays elements.

As variant you may make such particular aggregation in Power Query, but it depends on how the data is structured and what else you need to have in resulting table except mentioned data.

If speak about separate cells, perhaps for the converting text in one cell into the sum in another cell VBA will be better solution.

# Re: Split cell and Calculate formula

Assuming the example values you showed are in cells A1:C1, give this formula a try...

=SUMPRODUCT(B1*IFERROR(TRIM(MID(SUBSTITUTE("/"&SUBSTITUTE(C1,CHAR(10),"/"),"/",REPT(" ",300)),2*ROW(INDIRECT("1:"&1+LEN(C1)-LEN(SUBSTITUTE(C1,CHAR(10),""))))*300,300)),0))

# Re: Split cell and Calculate formula

That will be 2600*(22+1+2), but not 2600*(22*(value found based on text before 22)+1*(value found based on text before 1)+2*(value found based on text before 2) )

# Re: Split cell and Calculate formula

I did not get the idea from the OP's formula that he was conditioning which numbers to use based on the text before the number. What it looked like to me was that the OP wanted to multiply each number in cell C1 by the value in cell B1 and then add them up. My code is equivalent to that. Based on your interpretation of what the OP wants, what value do you see as the desired result?

EDIT NOTE: Ignore the above response... I reread the OP's message again and see what you mean. My earlier posted formula does not do what I now see as the OP's question. Thanks for noting this for me.

# Re: 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

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.

# Re: Split cell and Calculate formula

@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)