Microsoft spreadsheet head ****

HawkEye244

HawkEye244

Joined
18 Aug 2008
Messages
3,834
Reaction score
276
Location
Devon
Country
United Kingdom
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.
 
HawkEye244 said:
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.
Click to expand...

Use the ISNUMBER function.

So in C1 =SUM((ISNUMBER (A1),A1,0)*B1)
 
Sorry buggered that up a bit.

In C1 type =IF(ISNUMBER(B1),B1*A1,0)
 
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 ??
 
HawkEye244 said:
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 ??
Click to expand...

It means if B1 Is a number, multiply B1 by A1, if not just say it's zero (can be anything you want)
 
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.
 
EddieM said:
It means if B1 Is a number, multiply B1 by A1, if not just say it's zero (can be anything you want)
Click to expand...

I don't understand why it needs to be told A1 is zero if it's not another number???
 
Notch7 said:
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.
Click to expand...

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
 
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!
 
Last edited:
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")
 
Not working for me guys, I've tried them all. Think I'm going to give up now
 
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.
upload_2019-2-8_10-57-49.png


It doesn't like negative numbers.
 
Last edited:
Trouble with a lot of these solutions without have to revert to VBA is things like decimal points multiple numbers etc.
 
You must log in or register to reply here.
Back
Top