Microsoft spreadsheet head ****

[HawkEye244]

I want to multiply one cell by another cell like this

SUM(A1*B1)=C1

But in this case B1 contains a text word,

So how do I get the formula to work but disregard the text word within the cell?

I have spent the best part of an hour searching for solutions online for none of them to work.

 

[EddieM]

I want to multiply one cell by another cell like this

SUM(A1*B1)=C1

But in this case B1 contains a text word,

So how do I get the formula to work but disregard the text word within the cell?

I have spent the best part of an hour searching for solutions online for none of them to work.

Use the ISNUMBER function.

So in C1 =SUM((ISNUMBER (A1),A1,0)*B1)

 

[EddieM]

Sorry buggered that up a bit.

In C1 type =IF(ISNUMBER(B1),B1*A1,0)

 

[HawkEye244]

Eddie I appreciate the help. I understand the SUM function , you're asking the sum of one cell multiplied by the other but that's pretty much it .

The IF function is saying if B1 contains a number to multiple A1 by the number in B1 ? Is that what it means? I don't understand the ,0 bit ??

 

[EddieM]

Eddie I appreciate the help. I understand the SUM function , you're asking the sum of one cell multiplied by the other but that's pretty much it .

The IF function is saying if B1 contains a number to multiple A1 by the number in B1 ? Is that what it means? I don't understand the ,0 bit ??

It means if B1 Is a number, multiply B1 by A1, if not just say it's zero (can be anything you want)

 

[Notch7]

I thought Isnumber only checjed if the cell content contained a number.

If I understand Hawkeye wants to extract the number part in the cell as part of a calculation.

Is the cell content a combined data ie, a number with text added eg 'days'

You cant use a formula if the cell is formatted as text -the number part will recognised as text.

 

[HawkEye244]

It means if B1 Is a number, multiply B1 by A1, if not just say it's zero (can be anything you want)

I don't understand why it needs to be told A1 is zero if it's not another number???

 

[HawkEye244]

I thought Isnumber only checjed if the cell content contained a number.

If I understand Hawkeye wants to extract the number part in the cell as part of a calculation.

Is the cell content a combined data ie, a number with text added eg 'days'

You cant use a formula if the cell is formatted as text -the number part will recognised as text.

Apparently if you search online there are discussions of formulas that disregard the text within the cell and only recognise the number i.e 15 days.

P.s. yes you're right that's what I'm getting at

 

[rsgaz]

Holy cow, I have no idea how this works, I just found it on Google, but it works...

=SUMPRODUCT(MID(0&B1,LARGE(INDEX(ISNUMBER(--MID(B1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Just change both the 'B1' in that formula to the cell you want.

edit: I've noticed it only works for strings up to 25 characters. If you need more, just change all the 25s to a higher number!

 

[EddieM]

ok try this in C1 =IF(ISNUMBER(LOOKUP(9.999999999999E+307,LEFT(B1,ROW(B$1:B1000))+0)),A1*LOOKUP(9.999999999999E+307,LEFT(B1,ROW(B$1:B$1000))+0),"Not a number")

 

[RichA]

You could try this: https://exceljet.net/formula/strip-non-numeric-characters

 

[HawkEye244]

Not working for me guys, I've tried them all. Think I'm going to give up now

 

[jasonzyx]

As RichA suggests break the problem down into stages.
First strip out the text characters, or isolate the numbers. Do this in a cell, then deal with the result into a different cell to do the calculation with the other cell.
Only when you have successfully completed both parts separately, join the two functions together in the desired target cell.
NB the Textjoin function is relatively new.

TEXTJOIN function, new in Excel 2016
​

Pay special attention to the syntax. One minor mistake will cause errors or unexpected results.

Rsgaz's solution seems to work. Copy and paste it. then work from there for the multiplication.

It doesn't like negative numbers.

 

[jasonzyx]

Interesting result with Rsgaz's solution

 

[EddieM]

Trouble with a lot of these solutions without have to revert to VBA is things like decimal points multiple numbers etc.