Forum Discussion
Calculate text formula
Gerard_Jimenez Then you'd need som VBA coding. A small user defined function that I called TxtForm. It takes a text string that looks like a formula and "translates" it into a real formula. See attached.
I left the helper columns in there so that you can see how the text string in column E is constructed.
Hi, thanks for your prompt solution, but that wouldn't help as it is more of a manual formula.
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.
I need something more like:
=(value_before_X)*("convert_x_to_value",10,)+(number)
If no value before X, assume 1, if no X at all, use only number after +.
Gerard_Jimenez Then you'd need som VBA coding. A small user defined function that I called TxtForm. It takes a text string that looks like a formula and "translates" it into a real formula. See attached.
I left the helper columns in there so that you can see how the text string in column E is constructed.
- That did it.
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:
Column 😧 =IF(ISNUMBER(FIND("x",C11)),FIND("x",C11),0)
Columen E: =IF(D11=0,C11,(IF(D11=2,LEFT(C11)&$B$1,LEFT(C11,D11-1)&"*"&$B$1)&RIGHT(C11,LEN(C11)-D11)))
I guess it's not the most elegant solution, but I'm not an expert like you.
Thanks a lot for your help.
