Forum Discussion
Hi! I Need help with a formula in Excel
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.
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.
