Microsoft spreadsheet head ****
- Thread starter HawkEye244
- Start date
E
EddieM
Give a couple of example rows that are causing the issue.
- Joined
- 24 Sep 2005
- Messages
- 6,345
- Reaction score
- 268
- 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--
=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:
Doesn't work for me.
E
EddieM
Is that what the B column is consistently I.e. 1bag 5bag 10bag or does the mix of numbers and letters vary?
Well the word bag doesn't vary but the numbers may vary.. nothing so far has worked
- Joined
- 24 Sep 2005
- Messages
- 6,345
- Reaction score
- 268
- 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.
https://www.ablebits.com/office-addins-blog/2017/11/22/excel-extract-number-from-string/
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-
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.
https://www.ablebits.com/office-addins-blog/2017/11/22/excel-extract-number-from-string/
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:
- Joined
- 24 Sep 2005
- Messages
- 6,345
- Reaction score
- 268
- Country
And here is presumably what you think you want ?
Slightly modded from https://www.ablebits.com/office-addins-blog/2017/11/22/excel-extract-number-from-string/
See 'How to get number from any position in a string.'
Not so short, produces absolute (non signed) result, resolves the likes of '6bag10' to 610 Always have to be aware of limitations - there are probably more.
Used 'the now retired Excel 2007'.
-0-
Slightly modded from https://www.ablebits.com/office-addins-blog/2017/11/22/excel-extract-number-from-string/
See 'How to get number from any position in a string.'
Not so short, produces absolute (non signed) result, resolves the likes of '6bag10' to 610 Always have to be aware of limitations - there are probably more.
Used 'the now retired Excel 2007'.
-0-
Last edited:
We have a winner:
Very well done guys. The perseverance paid off!!
Very well done guys. The perseverance paid off!!
---
- Joined
- 24 Sep 2005
- Messages
- 6,345
- Reaction score
- 268
- 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-
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: