1. Visiting from the US? Why not try DIYnot.US instead? Click here to continue to DIYnot.US.
    Dismiss Notice

Microsoft spreadsheet head ****

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

  1. Notch7

    Notch7

    Joined:
    15 Sep 2017
    Messages:
    7,268
    Thanks Received:
    655
    Location:
    Sussex
    Country:
    United Kingdom
    your simplest answer would be to stick the text part in the adjacent cell -then do your calcs on the number only.
     
  2. EddieM

    EddieM

    Joined:
    15 Feb 2009
    Messages:
    5,881
    Thanks Received:
    205
    Country:
    United Kingdom
    Give a couple of example rows that are causing the issue.
     
    • Like Like x 1
  3. empip

    empip

    Joined:
    24 Sep 2005
    Messages:
    6,074
    Thanks Received:
    132
    Country:
    United Kingdom
    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

    HawkEye244

    Joined:
    18 Aug 2008
    Messages:
    3,384
    Thanks Received:
    233
    Location:
    London
    Country:
    United Kingdom
    K.jpg

    Doesn't work for me.
     
  5. EddieM

    EddieM

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

    HawkEye244

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

    empip

    Joined:
    24 Sep 2005
    Messages:
    6,074
    Thanks Received:
    132
    Country:
    United Kingdom
    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: 9 Feb 2019
  8. empip

    empip

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

    diynot excel problem2.jpg

    -0-
     
  9. empip

    empip

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

    HawkEye244

    Joined:
    18 Aug 2008
    Messages:
    3,384
    Thanks Received:
    233
    Location:
    London
    Country:
    United Kingdom
  11. rsgaz

    rsgaz

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

    HawkEye244

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

    most excellent.jpg

    Very well done guys. The perseverance paid off!!
     
  13. HawkEye244

    HawkEye244

    Joined:
    18 Aug 2008
    Messages:
    3,384
    Thanks Received:
    233
    Location:
    London
    Country:
    United Kingdom
  14. empip

    empip

    Joined:
    24 Sep 2005
    Messages:
    6,074
    Thanks Received:
    132
    Country:
    United Kingdom
    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: 12 Feb 2019 at 7:05 AM
Loading...

Share This Page