enter a numeric value and date in a cell.

[shaju]

Is it possible that I can enter a numeric value and a date in the same cell, but only the numeric value is taken for calculations.
say, Cell A1 has "100", and Cell B1 has 25 - 03-09-17 in it . What I need in cell C1 is 100-25 = 75. (the result 75 only). 25 is the sale done on 03-09-17, and now I am entering the date in the comment box. does somebody know some way or format so that only a part of values entered into a cell is used for calculations. Even new idea to enter a value and date in the same cell might help me (as long as the answer to my question given above is 75)

 

[etaf]

will the number always be followed by a -
if so
=LEFT(B1,SEARCH("-",B1,1)-1)*1
Will give you the number

=A1 - (LEFT(B1,SEARCH("-",B1,1)-1)*1)

 

[shaju]

Thank You etaf for the quick response. I should have made it clear.....Sorry for troubling with only a part of the question. The problem is that the cells which I need to have numeric value and date is not just one cell, it is a range from B1:k1. I should have made this clear. So the formula in cell L1 runs like this. "=A1- sum(b1:k1)". now the question is full.

 

[etaf]

ok, so thats going to be a lot more difficult
an array may work

=SUMPRODUCT(1*LEFT(B1:K1,FIND("-",B1,1)-1))

OR
IF NOT ALL CELLS have values
=SUMPRODUCT(IF(ISNUMBER(VALUE(LEFT(b1:k1,FIND("-",b1,1)-1))),1*LEFT(b1:k1,FIND("-",b1,1)-1),0))

use
control + shift + enter to get an array
{=SUMPRODUCT(IF(ISNUMBER(VALUE(LEFT(b1:k1,FIND("-",b1,1)-1))),1*LEFT(b1:k1,FIND("-",b1,1)-1),0))}

 

[Rick Rothstein]

ok, so thats going to be a lot more difficult an array may work

=SUMPRODUCT(1*LEFT(B1:K1,FIND("-",B1:K1,1)-1))

You need the part I show in red in case the first number has less digits in it than the number in cell B1.

OR
IF NOT ALL CELLS have values
=SUMPRODUCT(IF(ISNUMBER(VALUE(LEFT(b1:k1,FIND("-",b1,1)-1))),1*LEFT(b1:k1,FIND("-",b1,1)-1),0))

use
control + shift + enter to get an array
{=SUMPRODUCT(IF(ISNUMBER(VALUE(LEFT(b1:k1,FIND("-",b1,1)-1))),1*LEFT(b1:k1,FIND("-",b1,1)-1),0))}

I think this shorter, normally-entered formula will also work...

=SUMPRODUCT(0+LEFT(B1:K1&0,FIND("-",B1:K1&"0-")-1))