Discussion Calculate text formula in Excel
https://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2244636#M94749
<P>Hi all, have been looking all the options for this scenario but haven't found any solution, or at least I have not been able to understand them.</P><P> </P><P>I have a formula set that I wish to calculate, where x should be replaced by a value, let's say 10, and calculated as per this example:</P><P> </P><TABLE border="1" width="100%"><TBODY><TR><TD><STRONG>Formula</STRONG></TD><TD><STRONG>Result</STRONG></TD></TR><TR><TD width="50%">1</TD><TD width="50%">1</TD></TR><TR><TD width="50%">2</TD><TD width="50%">2</TD></TR><TR><TD width="50%">x</TD><TD width="50%">10</TD></TR><TR><TD width="50%">x+2</TD><TD width="50%">12</TD></TR><TR><TD width="50%">2x+1</TD><TD width="50%">21</TD></TR><TR><TD width="50%">3x+5</TD><TD width="50%">35</TD></TR><TR><TD width="50%">10x+20</TD><TD width="50%">120</TD></TR></TBODY></TABLE><P> </P><P>Any help would be greatly appreciated.</P>Tue, 30 Mar 2021 14:39:05 GMTGerard_Jimenez2021-03-30T14:39:05ZCalculate text formula
https://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2244636#M94749
<P>Hi all, have been looking all the options for this scenario but haven't found any solution, or at least I have not been able to understand them.</P><P> </P><P>I have a formula set that I wish to calculate, where x should be replaced by a value, let's say 10, and calculated as per this example:</P><P> </P><TABLE border="1" width="100%"><TBODY><TR><TD><STRONG>Formula</STRONG></TD><TD><STRONG>Result</STRONG></TD></TR><TR><TD width="50%">1</TD><TD width="50%">1</TD></TR><TR><TD width="50%">2</TD><TD width="50%">2</TD></TR><TR><TD width="50%">x</TD><TD width="50%">10</TD></TR><TR><TD width="50%">x+2</TD><TD width="50%">12</TD></TR><TR><TD width="50%">2x+1</TD><TD width="50%">21</TD></TR><TR><TD width="50%">3x+5</TD><TD width="50%">35</TD></TR><TR><TD width="50%">10x+20</TD><TD width="50%">120</TD></TR></TBODY></TABLE><P> </P><P>Any help would be greatly appreciated.</P>Tue, 30 Mar 2021 14:39:05 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2244636#M94749Gerard_Jimenez2021-03-30T14:39:05ZRe: Calculate text formula
https://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2244649#M94751
<P><LI-USER uid="1012652"></LI-USER> </P><P>Perhaps like in the attached workbook?</P><P> </P>Tue, 30 Mar 2021 14:43:54 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2244649#M94751Riny_van_Eekelen2021-03-30T14:43:54ZRe: Calculate text formula
https://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2244676#M94753
<P><LI-USER uid="403176"></LI-USER> </P><P> </P><P>Hi, thanks for your prompt solution, but that wouldn't help as it is more of a manual formula.</P><P> </P><P>To further elaborate, I have 1,750 lines, so typing a formula for each line is not a practical solution, on the other hand, if some formulas change, the result should be recalculated accordingly.</P><P> </P><P>I need something more like:</P><P> </P><P>=(value_before_X)*("convert_x_to_value",10,)+(number)</P><P> </P><P>If no value before X, assume 1, if no X at all, use only number after +.</P>Tue, 30 Mar 2021 15:02:37 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2244676#M94753Gerard_Jimenez2021-03-30T15:02:37ZRe: Calculate text formula
https://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2245112#M94768
<P><LI-USER uid="1012652"></LI-USER> Then you'd need som VBA coding. A small user defined function that I called <FONT color="#FF0000">TxtForm.<FONT color="#000000"> It takes</FONT></FONT> a text string that <EM><STRONG>looks like</STRONG></EM> a formula and "translates" it into a real formula. See attached.</P><P>I left the helper columns in there so that you can see how the text string in column E is constructed.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Screenshot 2021-03-30 at 20.30.04.png" style="width: 491px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/268302iD93A91B8A854214B/image-size/large?v=v2&px=999" role="button" title="Screenshot 2021-03-30 at 20.30.04.png" alt="Screenshot 2021-03-30 at 20.30.04.png" /></span></P><P> </P><P> </P><P> </P>Tue, 30 Mar 2021 18:35:05 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2245112#M94768Riny_van_Eekelen2021-03-30T18:35:05ZRe: Calculate text formula
https://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2247040#M94855
That did it.<BR /><BR />I just needed to modify it a bit, in cases where there's no X, just the number. So I modified the helper columns like this:<BR /><BR />Column <LI-EMOJI id="lia_anguished-face" title=":anguished_face:"></LI-EMOJI> =IF(ISNUMBER(FIND("x",C11)),FIND("x",C11),0)<BR /><BR />Columen E: =IF(D11=0,C11,(IF(D11=2,LEFT(C11)&$B$1,LEFT(C11,D11-1)&"*"&$B$1)&RIGHT(C11,LEN(C11)-D11)))<BR /><BR />I guess it's not the most elegant solution, but I'm not an expert like you.<BR /><BR />Thanks a lot for your help.Wed, 31 Mar 2021 14:03:02 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2247040#M94855Gerard_Jimenez2021-03-31T14:03:02ZRe: Calculate text formula
https://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2247059#M94857
<P><LI-USER uid="1012652"></LI-USER> Elegance doesn't really matter. Like <EM><STRONG>beauty</STRONG></EM>, it's in the eye of the beholder. All that matters is that you succeeded to adopt my formulae by yourself and that it works for you.</P>Wed, 31 Mar 2021 14:11:42 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-text-formula/m-p/2247059#M94857Riny_van_Eekelen2021-03-31T14:11:42Z