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)

`12*14` can be solved but using VBA macro and the NUMBERVALUE can't solve this! — Rajesh Sinha, Jun 07 '20 at 06:55

score 1 · Answer 1 · edited Jun 07 '20 at 10:33

1

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)

edited Jun 07 '20 at 10:33

Reddy Lutonadio

answered Jun 07 '20 at 10:25

This is good, But i have to perform for every cell i.e. B - C and it does not work drag down, like function work in Excel – Abhishek Jun 07 '20 at 10:36

Gary's Student · Accepted Answer · 2020-06-07T14:31:34.407

1

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

edited Jun 07 '20 at 14:31

answered Jun 07 '20 at 10:38

Gary's Student

2 Answers2