 SOLVED

# Hi! I Need help with a formula in Excel

Occasional Contributor

# Hi! I Need help with a formula in Excel

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!

5 Replies
best response confirmed by Kristian Lawrence Caringal (Occasional Contributor)
Solution

# Re: 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,",","+"),"@","*")))`

`=GetResult`

Side effect in both cases you shall keep your workbook as macro enabled one. # Re: Hi! I Need help with a formula in Excel

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.

# Re: Hi! I Need help with a formula in Excel

Thank you very much for the help!

# Re: Hi! I Need help with a formula in Excel

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

# Re: Hi! I Need help with a formula in Excel

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