SOLVED

Hi! I Need help with a formula in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-141982%22%20slang%3D%22en-US%22%3EHi!%20I%20Need%20help%20with%20a%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-141982%22%20slang%3D%22en-US%22%3E%3CP%3EGiven(for%20example%20in%20cell%20A1)%3A%20%3CSTRONG%3E1%4050%2C10%40100%2C15%40250%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EProblem%3A%3C%2FP%3E%0A%3CP%3EPerform%20the%20following%20formula%20(for%20example%20in%20cell%20A2)%3A%201*50%20%2B%2010*100%2B15*250%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20anyone%20help%20and%20provide%20the%20combination%20of%20functions%20which%20can%20do%20the%20formula%20I%20intend%20to%20do%3F%20The%20given%20example%20is%20in%20a%20single%20cell%20and%20the%20solution%20I%20prefer%20should%20also%20be%20in%20a%20single%20cell%20(which%20should%20result%20to%204800).%20For%20Clarity%20I%20posted%20an%20attachment%20of%20the%20actual%20given%20and%20result%20I%20desire.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20very%20much!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-141982%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-142474%22%20slang%3D%22en-US%22%3ERe%3A%20Hi!%20I%20Need%20help%20with%20a%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-142474%22%20slang%3D%22en-US%22%3EThanks%20for%20this.%20I%20used%20a%20macro%20instead%20of%20simplicity.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-142473%22%20slang%3D%22en-US%22%3ERe%3A%20Hi!%20I%20Need%20help%20with%20a%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-142473%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much%20for%20the%20help!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-142127%22%20slang%3D%22en-US%22%3ERe%3A%20Hi!%20I%20Need%20help%20with%20a%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-142127%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20have%20time%2C%20you%20can%20build%20the%20formula.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20common%20knowledge%20is%20that%2C%20with%26nbsp%3B%3CSTRONG%3ESUBSTITUTE-function%3C%2FSTRONG%3E%20(%3CA%20title%3D%22SUBSTITUTE-function%22%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FSUBSTITUTE-function-6434944e-a904-4336-a9b0-1e58df3bc332%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FSUBSTITUTE-function-6434944e-a904-4336-a9b0-1e58df3bc332%3C%2FA%3E)%2C%20you%20can%20replace%20a%3CSPAN%3E%26nbsp%3Bspecific%20text%20with%20another%20text%20string.%26nbsp%3B%20This%20function%20can%20help%20another%20function%26nbsp%3B%3CSTRONG%3EFIND-FINDB-functions%3C%2FSTRONG%3E%26nbsp%3B(%3CA%20title%3D%22FIND-FINDB-functions%22%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FFIND-FINDB-functions-c7912941-af2a-4bdf-a553-d0d89b0a0628%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FFIND-FINDB-functions-c7912941-af2a-4bdf-a553-d0d89b0a0628%3C%2FA%3E).%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ETo%20find%20the%20Nth%20occurance%20location%20of%20a%20specific%20text%20in%20another%20text%20cannot%20be%20done%20by%20Find%20function%20itself%20only.%26nbsp%3B%20However%20we%20can%20do%20this%2C%20e.g.%20to%20find%20the%202nd%20%22%2C%22%20in%20A1%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3E%3DFIND(%22%24%22%2C%20SUBSTITUTE(A1%2C%20%22%2C%22%2C%20%22%24%22%2C%202))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3Ewhere%26nbsp%3BSUBSTITUTE(A1%2C%20%22%2C%22%2C%20%22%24%22%2C%202)%20will%20turn%20your%20A1%20cell%20to%20%221%4050%2C10%40100%2415%40250%22%2C%20and%20since%20this%20text%20contains%20only%201%20dollar%20sign%20(%22%24%22)%2C%20the%20find%20function%20can%20locate%20it%2C%20which%20was%20the%20original%20second%20comma.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIn%20addition%2C%20LEFT-function%2C%20RIGHT-function%20and%20MID-MIDB-function%20can%20help%20you%20to%20extract%20a%20specific%20text%20for%20your%20operations.%26nbsp%3B%20Then%2C%20you%20can%20get%20the%20formula.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3E%3DVALUE(LEFT(A1%2CFIND(%22%40%22%2CA1)-1))*VALUE(RIGHT(LEFT(A1%2CFIND(%22%2C%22%2CA1)-1)%2CLEN(LEFT(A1%2CFIND(%22%2C%22%2CA1)-1))-FIND(%22%40%22%2CA1))%2BVALUE(LEFT(MID(LEFT(A1%2CFIND(%22%24%22%2CSUBSTITUTE(A1%2C%22%2C%22%2C%22%24%22%2C2))-1)%2CFIND(%22%2C%22%2CA1)%2B1%2C255)%2CFIND(%22%40%22%2CMID(LEFT(A1%2CFIND(%22%24%22%2CSUBSTITUTE(A1%2C%22%2C%22%2C%22%24%22%2C2))-1)%2CFIND(%22%2C%22%2CA1)%2B1%2C255))-1))*VALUE(RIGHT(MID(LEFT(A1%2CFIND(%22%24%22%2CSUBSTITUTE(A1%2C%22%2C%22%2C%22%24%22%2C2))-1)%2CFIND(%22%2C%22%2CA1)%2B1%2C255)%2CLEN(MID(LEFT(A1%2CFIND(%22%24%22%2CSUBSTITUTE(A1%2C%22%2C%22%2C%22%24%22%2C2))-1)%2CFIND(%22%2C%22%2CA1)%2B1%2C255))-FIND(%22%40%22%2CMID(LEFT(A1%2CFIND(%22%24%22%2CSUBSTITUTE(A1%2C%22%2C%22%2C%22%24%22%2C2))-1)%2CFIND(%22%2C%22%2CA1)%2B1%2C255))))%2BVALUE(LEFT(MID(A1%2CFIND(%22%24%22%2CSUBSTITUTE(A1%2C%22%2C%22%2C%22%24%22%2C2))%2B1%2C255)%2CFIND(%22%40%22%2CMID(A1%2CFIND(%22%24%22%2CSUBSTITUTE(A1%2C%22%2C%22%2C%22%24%22%2C2))%2B1%2C255))-1))*VALUE(RIGHT(A1%2CLEN(A1)-FIND(%22%24%22%2CSUBSTITUTE(A1%2C%22%40%22%2C%22%24%22%2C3)))))%0A%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3BI%20have%20no%20time%20and%20idea%20on%20how%20to%20simplify%20the%20above%20formula%2C%20but%20you%20can%20use%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-141995%22%20slang%3D%22en-US%22%3ERe%3A%20Hi!%20I%20Need%20help%20with%20a%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-141995%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Kristian%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETwo%20ways%20what%20I%20know%2C%20both%20not%20quite%20suitable.%3C%2FP%3E%0A%3CP%3EFirst%2C%20use%20macro%20with%20Evaluate%20VBA%20function%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F1683-excel-convert-text-string-to-formula.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F1683-excel-convert-text-string-to-formula.html%3C%2FA%3E%3C%2FP%3E%0A%3CP%3ESecond%20use%20undocumented%20EVALUATE%20function%20without%20the%20macro%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.vertex42.com%2FExcelArticles%2Fevaluate-function.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.vertex42.com%2FExcelArticles%2Fevaluate-function.html%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20both%20cases%20you%20have%20to%20convert%20your%20string%20into%20the%20text%20with%20formula%2C%20for%20the%20second%20approach%26nbsp%3Bcould%20be%20done%20as%3C%2FP%3E%0A%3CPRE%3E%3D(SUBSTITUTE(SUBSTITUTE(A1%2C%22%2C%22%2C%22%2B%22)%2C%22%40%22%2C%22*%22))%3C%2FPRE%3E%0A%3CP%3EWith%20that%20in%20name%20manager%20you%20could%20define%20the%20name%2C%20let%20say%20GetResult%2C%20as%3C%2FP%3E%0A%3CPRE%3E%3DEVALUATE((SUBSTITUTE(SUBSTITUTE(Sheet1!%24A%241%2C%22%2C%22%2C%22%2B%22)%2C%22%40%22%2C%22*%22)))%3C%2FPRE%3E%0A%3CP%3Eand%20receive%20that%20result%20as%3C%2FP%3E%0A%3CPRE%3E%3DGetResult%3C%2FPRE%3E%0A%3CP%3ESide%20effect%20in%20both%20cases%20you%20shall%20keep%20your%20workbook%20as%20macro%20enabled%20one.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Evaluate.JPG%22%20style%3D%22width%3A%20516px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F26419i6C5DB681DA008B62%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Evaluate.JPG%22%20alt%3D%22Evaluate.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1913883%22%20slang%3D%22en-US%22%3ERe%3A%20Hi!%20I%20Need%20help%20with%20a%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1913883%22%20slang%3D%22en-US%22%3Ehi%20i%20didn't%20know%20how%20to%20it%20i%20think%20am%20gonna%20need%20moore%20help%3C%2FLINGO-BODY%3E
Occasional Contributor

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

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.

Evaluate.JPG

 

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.

 

Thank you very much for the help!
Thanks for this. I used a macro instead of simplicity.
hi i didn't know how to it i think am gonna need moore help