Discussion in 'Software' started by HawkEye244, 6 Feb 2019.

Joined:
15 Sep 2017
Messages:
7,268
655
Location:
Sussex
Country:

2. ### EddieM

Joined:
15 Feb 2009
Messages:
5,881
205
Country:
Give a couple of example rows that are causing the issue.

• Like x 1
3. ### empip

Joined:
24 Sep 2005
Messages:
6,074
132
Country:
Type or copy into C3
=IF(ISNUMBER(A3)*ISNUMBER(B3)=1,A3*B3,0)
This will result in a zero answer if one cell or both contain text.

It means IF the first multiplication is true (both cells contain numbers) or =1 here, THEN multiply a3*b3 , ELSE give a zero answer.
What other value do we give some unknown text times a number ?

-0-
Edit:-
=IF(ISTEXT(A3)+ISTEXT(B3)=2,"All Text",IF(ISNUMBER(A3)+ISNUMBER(B3)=2,A3*B3,IF(ISNUMBER(A3)=TRUE,A3,B3)))
If both cells have numeric values, this will multiply A3 and B3 , if only one is numeric that will be the answer, if both are text the result will be 'All Text'.

--0--

Last edited: 8 Feb 2019
4. ### HawkEye244

Joined:
18 Aug 2008
Messages:
3,384
233
Location:
London
Country:

Doesn't work for me.

5. ### EddieM

Joined:
15 Feb 2009
Messages:
5,881
205
Country:
Is that what the B column is consistently I.e. 1bag 5bag 10bag or does the mix of numbers and letters vary?

6. ### HawkEye244

Joined:
18 Aug 2008
Messages:
3,384
233
Location:
London
Country:
Well the word bag doesn't vary but the numbers may vary.. nothing so far has worked

7. ### empip

Joined:
24 Sep 2005
Messages:
6,074
132
Country:
So do you require 2 x 2bag to equal 4 OR as in my suggestion (below) '2bag' to be counted as zero ?
If you need 4 as an answer you need to extract the numeric from the text string ie. 2bag becomes 2.
Better to have, as suggested elsewhere, the text 'bag' etc in separate columns.

One way to extract a number from a string in Excel.
Another method would be placing the text part in an adjacent column / cell in the first place.

edit :- Just noticed the win10 screen clip to DiyNot is not showing correctly '=' looks like minus... Hmmm.
-0-

Last edited: 9 Feb 2019
8. ### empip

Joined:
24 Sep 2005
Messages:
6,074
132
Country:
Same image as .JPG and not .PNG - Better job.

-0-

9. ### empip

Joined:
24 Sep 2005
Messages:
6,074
132
Country:
Last edited: 10 Feb 2019
• Thanks x 1

Joined:
18 Aug 2008
Messages:
3,384
233
Location:
London
Country:

11. ### rsgaz

Joined:
28 Jul 2014
Messages:
1,301
352
Country:
You haven't times it by B2 yet though! Put all of it except the equals in brackets, then add *B2

12. ### HawkEye244

Joined:
18 Aug 2008
Messages:
3,384
233
Location:
London
Country:
We have a winner:

Very well done guys. The perseverance paid off!!

Joined:
18 Aug 2008
Messages:
3,384
233
Location:
London
Country:
---

14. ### empip

Joined:
24 Sep 2005
Messages:
6,074
132
Country:
This takes number from left of string only in either column ie. 2bag = 2, bag2 = error (#VALUE!) It multiplies the two cells. at the position ')*(' below in formula bar.
VALUE was used in case the functions returned an extracted numeric as a text string - It actually produces a number even tho' under normal alignment in the cell the result is positioned as text to the left, numbers normally to the right.

Anyhow overall, Google came to the rescue with www.ablebits.com !
-0-

Last edited: 12 Feb 2019 at 7:05 AM

HawkEye244, in forum: Software
Replies:
2
Views:
95
2. ### Microsoft Visual C+

maltaron, in forum: Software
Replies:
2
Views:
442

nickso, in forum: Software
Replies:
4
Views:
611
4. ### Does your Microsoft Edge do this?

cjard, in forum: Software
Replies:
3
Views:
615
5. ### Microsoft Account

securespark, in forum: Software
Replies:
6
Views:
524

Replies:
10
Views:
907
7. ### Microsoft Office 365

Humph, in forum: Software
Replies:
6
Views:
753