Hi! I Need help with a formula in Excel

[Kristian Lawrence Caringal]

Given(for example in cell A1): 1@50,10@100,15@250

Problem:

Perform the following formula (for example in cell A2): 1*50 + 10*100+15*250

Can anyone help and provide the combination of functions which can do the formula I intend to do? The given example is in a single cell and the solution I prefer should also be in a single cell (which should result to 4800). For Clarity I posted an attachment of the actual given and result I desire. 

Thank you very much! 

[#2 · Accepted solution]

Hi Kristian,

Two ways what I know, both not quite suitable.

First, use macro with Evaluate VBA function https://www.extendoffice.com/documents/excel/1683-excel-convert-text-string-to-formula.html

Second use undocumented EVALUATE function without the macro https://www.vertex42.com/ExcelArticles/evaluate-function.html

In both cases you have to convert your string into the text with formula, for the second approach could be done as

=(SUBSTITUTE(SUBSTITUTE(A1,",","+"),"@","*"))

With that in name manager you could define the name, let say GetResult, as

=EVALUATE((SUBSTITUTE(SUBSTITUTE(Sheet1!$A$1,",","+"),"@","*")))

and receive that result as

=GetResult

Side effect in both cases you shall keep your workbook as macro enabled one.

[#3]

If you have time, you can build the formula.

The common knowledge is that, with SUBSTITUTE-function (https://support.office.com/en-us/article/SUBSTITUTE-function-6434944e-a904-4336-a9b0-1e58df3bc332), you can replace a specific text with another text string.  This function can help another function FIND-FINDB-functions (https://support.office.com/en-us/article/FIND-FINDB-functions-c7912941-af2a-4bdf-a553-d0d89b0a0628).

To find the Nth occurance location of a specific text in another text cannot be done by Find function itself only.  However we can do this, e.g. to find the 2nd "," in A1

=FIND("$", SUBSTITUTE(A1, ",", "$", 2))

where SUBSTITUTE(A1, ",", "$", 2) will turn your A1 cell to "1@50,10@100$15@250", and since this text contains only 1 dollar sign ("$"), the find function can locate it, which was the original second comma.

In addition, LEFT-function, RIGHT-function and MID-MIDB-function can help you to extract a specific text for your operations.  Then, you can get the formula.

=VALUE(LEFT(A1,FIND("@",A1)-1))*VALUE(RIGHT(LEFT(A1,FIND(",",A1)-1),LEN(LEFT(A1,FIND(",",A1)-1))-FIND("@",A1))+VALUE(LEFT(MID(LEFT(A1,FIND("$",SUBSTITUTE(A1,",","$",2))-1),FIND(",",A1)+1,255),FIND("@",MID(LEFT(A1,FIND("$",SUBSTITUTE(A1,",","$",2))-1),FIND(",",A1)+1,255))-1))*VALUE(RIGHT(MID(LEFT(A1,FIND("$",SUBSTITUTE(A1,",","$",2))-1),FIND(",",A1)+1,255),LEN(MID(LEFT(A1,FIND("$",SUBSTITUTE(A1,",","$",2))-1),FIND(",",A1)+1,255))-FIND("@",MID(LEFT(A1,FIND("$",SUBSTITUTE(A1,",","$",2))-1),FIND(",",A1)+1,255))))+VALUE(LEFT(MID(A1,FIND("$",SUBSTITUTE(A1,",","$",2))+1,255),FIND("@",MID(A1,FIND("$",SUBSTITUTE(A1,",","$",2))+1,255))-1))*VALUE(RIGHT(A1,LEN(A1)-FIND("$",SUBSTITUTE(A1,"@","$",3)))))

 I have no time and idea on how to simplify the above formula, but you can use it.

[#4]

Thank you very much for the help!

[#5]

Thanks for this. I used a macro instead of simplicity.

[#6]

hi i didn't know how to it i think am gonna need moore help