Microsoft spreadsheet head ****

your simplest answer would be to stick the text part in the adjacent cell -then do your calcs on the number only.
 
Sponsored Links
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:
K.jpg


Doesn't work for me.
 
Sponsored Links
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
 
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.

excel thing.png


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:
Same image as .JPG and not .PNG - Better job.

diynot excel problem2.jpg


-0-
 
Last edited:
You haven't times it by B2 yet though! Put all of it except the equals in brackets, then add *B2
 
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-

diynot prob3a.jpg
 
Last edited:
Sponsored Links
Back
Top