I have a text written as "12*14" in C4 and and number "1000" in B4. I want to calculate D4 as 1000-12*14
I have tried following, But both are not working here.
B4-NUMBERVALUE(C4)
B4-VALUE(C4)
You need to convert the Text into a Number. In D4 enter:
=B4-LEFT(C4,FIND("*",C4)-1)*MID(C4,FIND("*",C4)+1,99)
The factors:
LEFT(C4,FIND("*",C4)-1) isolates the 12
MID(C4,FIND("*",C4)+1,99) isolates the 14
Click on the "Formulas" tab in the ribbon and click on "Name Manager"
Click on "New" on the top right of the "Name Manger" window
Enter any name you want, I used "anything"
In the "Refers To" section type the following formula "=evaluate(Sheet1!$C$4)" and click "OK"
Close the "Name Manager" window
Go to the cell where you want to output the data (cell D4) in your case, then enter the following :
=B4-anything
(anything was the name I entered in the Name Manager)
12*14
can be solved but using VBA macro and the NUMBERVALUE can't solve this!