0

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)

3
  • Will the text always be in the form of number*number?
    – Jonathan
    Jun 7, 2020 at 6:29
  • 12*14 can be solved but using VBA macro and the NUMBERVALUE can't solve this! Jun 7, 2020 at 6:55
  • @Jonathan: Yes, It will be always like number*number
    – Abhishek
    Jun 7, 2020 at 10:12

2 Answers 2

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)

enter image description here

The factors:

LEFT(C4,FIND("*",C4)-1)     isolates the 12
MID(C4,FIND("*",C4)+1,99)   isolates the 14
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. 3.

IMG:

Enter any name you want, I used "anything"

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

  1. Close the "Name Manager" window

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

1
  • 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 7, 2020 at 10:36

Not the answer you're looking for? Browse other questions tagged .