evaluate text present in specific format in another column in Excel

Question

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)

Answer 1

1. Click on the "Formulas" tab in the ribbon and click on "Name Manager"

2. Click on "New" on the top right of the "Name Manger" window

3.

Enter any name you want, I used "anything"

In the "Refers To" section type the following formula "=evaluate(Sheet1!$C$4)" and click "OK"

4. Close the "Name Manager" window

5. 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)

Answer 2

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