Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Identifying text vs. a number in a cell question

    I have a number of cell formulas that depend upon seeing numbers in other cells. On occasion I'll have a reason to type a word into one of those other cells instead of a number and it makes any associated macros report an error while running.

    I'd like to add a qualifier to the front of the formulas so that if either text, or no number, is present then the formula is ignored.

    Something just like:

    =IF(A1="","")

    but instead of just having a set parenthesis indicating a result if there is an empty cell there would be a qualifier that either specifies if text is present then no cell input or if a number is present then the formula continues.

    I suspect this is rather straight forward I just have never run into it before.

    Thanks,
    BH

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Is the issue within a macro or on the sheet itself? ISNUMBER(cell) will return true or false depending on the cells format.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by kweaver View Post
    Is the issue within a macro or on the sheet itself? ISNUMBER(cell) will return true or false depending on the cells format.

    The issue is within the sheet........not the macro.

    I use ISNUMBER quite a lot but for specific text. Is there a way to use it for "any text"?

    Thanks,
    BH

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'm not clear to me what you mean by "any text."

    This is what I understand from your post: =IF(isnumber(cell), SOME_FORMULA,"") where if the cell does not contain a number, the result will be a blank. Change the "" to whatever you want to be there if the cell does not contain a number.

    Maybe post a sample file?!

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    BH,

    You mean something like this:

    check for numbers.PNG

    Formula: =IF(AND(ISNUMBER(C1),ISNUMBER(D1),ISNUMBER(E1)),C1 +D1+E1,0)

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by kweaver View Post
    I'm not clear to me what you mean by "any text."

    This is what I understand from your post: =IF(isnumber(cell), SOME_FORMULA,"") where if the cell does not contain a number, the result will be a blank. Change the "" to whatever you want to be there if the cell does not contain a number.

    Maybe post a sample file?!
    Thanks for bearing with me on this.

    Unfortunately a sample file isn't going to help here.

    I have complex sheets with a lot of cross references between cells. Various cells react different ways depending upon what is in other cells.

    All these cells depend upon number references. If one cell has 25 entered for example, and the one next to it has 35 entered, then several other cells will return specific number results based upon those entries. If the numbers go up or down in those first two cells then the formulas in the other cells with make different calculations and give different results.

    The occasional problem is that every now and then I'll need to enter text in one of those first 2 cells so the person using the print out of all this will get some textual information. This is when an error is returned in some of those other cells..............because the formulas in those cells are reacting to numbers.....not text.

    The text that could be typed in occasionally could be any word or words so I can't just specify a single word or group of words that will result in the cell with the text being ignored.

    I hope I've explained this a little more clearly now.

    Thanks,
    BH

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by RetiredGeek View Post
    BH,

    You mean something like this:

    check for numbers.PNG

    Formula: =IF(AND(ISNUMBER(C1),ISNUMBER(D1),ISNUMBER(E1)),C1 +D1+E1,0)

    HTH
    Well, that got me thinking a little more. Maybe an example would help.

    Here is the formula from from cell O27:

    =IF(ISNUMBER(SEARCH("NON",C14)),0,IF(OR(ISNUMBER(S EARCH("jamb",J16)), ISNUMBER(SEARCH("strip",J16)), C14="STRAIGHTS",C16="MDF",C16="PVC"),0,IF(C14="SER PENTINE",0,IF(C12="",0,IF(C14="ELLIPTICAL",J12+3,I F(C14="OVAL",J12+3,ROUND(AD20*16,0)/16))))))

    You'll see J12+3 referenced near the end of this formula. That is referencing the width of a part. Normally I will enter 3, 3 1/2, 4 etc as the width of the part but every now and then I want to enter something like "any width" in J12 for information on the printout. In that case I'd want cell O27 to remain blank or 0, but instead it returns an errror since J12 does not contain a number. And since other cells act in relation to what is in O27 I end up with errors in numerous places around the worksheet.

    Helpful?
    BH
    Last edited by bhdavis; 2016-08-10 at 17:28.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    BH,

    A simpler solution might be to just do this =IFError(YourRegularFormulaHere,ErrorValueHere)

    check for numbers.PNG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    RG's is simple. "IFERROR" was a nice function added to recent releases.

    Also, suppose you replaced the two instances of "J12+3" with if(isnumber(J12),J12+3,0)

  10. #10
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by kweaver View Post
    RG's is simple. "IFERROR" was a nice function added to recent releases.

    Also, suppose you replaced the two instances of "J12+3" with if(isnumber(J12),J12+3,0)


    Thanks everyone. I'll give both suggestions a try.
    BH

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi BH

    re:Identifying text vs. a number in a cell question
    ..perhaps you just need the =ISTEXT( function ???

    zeddy

  12. #12
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by zeddy View Post
    Hi BH

    re:Identifying text vs. a number in a cell question
    ..perhaps you just need the =ISTEXT( function ???

    zeddy

    That would be awesome if I could get it to work. I tried this at the beginning of a cell formula to no avail. Obviously I'm doing something wrong as it didn't return a blank cell. That is the same format I use on an ISNUMBER(SEARCH) function with a search phrase added so it was of course my first thought.


    =IF(ISTEXT (J12),"",IF(ISNUMBER(SEARCH("NON",C14)),0,IF(OR(IS NUMBER(SEARCH("jamb",J16)), ISNUMBER(SEARCH("strip",J16)), C14="STRAIGHTS",C16="MDF",C16="PVC"),0,IF(C14="SER PENTINE",0,IF(C12="",0,IF(C14="ELLIPTICAL",J12+3,I F(C14="OVAL",J12+3,ROUND(AD20*16,0)/16)))))))

    BH
    Last edited by bhdavis; 2016-08-11 at 15:36.

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi BH

    copy this and try it in cell [O27]
    Code:
    =IF(NOT(ISNUMBER(J12)),"",IF(ISNUMBER(SEARCH("NON",C14)),0,IF(OR(ISNUMBER(SEARCH("jamb",J16)), ISNUMBER(SEARCH("strip",J16)), C14="STRAIGHTS",C16="MDF",C16="PVC"),0,IF(C14="SERPENTINE",0,IF(C12="",0,IF(C14="ELLIPTICAL",J12+3,IF(C14="OVAL",J12+3,ROUND(AD20*16,0)/16)))))))
    ..let us know if this works?

    zeddy
    Last edited by zeddy; 2016-08-11 at 17:32.

  14. #14
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by zeddy View Post
    Hi BH

    copy this and try it in cell [O27]
    Code:
    =IF(NOT(ISNUMBER(J12)),"",IF(ISNUMBER(SEARCH("NON",C14)),0,IF(OR(ISNUMBER(SEARCH("jamb",J16)), ISNUMBER(SEARCH("strip",J16)), C14="STRAIGHTS",C16="MDF",C16="PVC"),0,IF(C14="SERPENTINE",0,IF(C12="",0,IF(C14="ELLIPTICAL",J12+3,IF(C14="OVAL",J12+3,ROUND(AD20*16,0)/16)))))))
    ..let us know if this works?

    zeddy
    Thanks, I'll give it a try in the morning.
    BH

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    bh,

    In Post 12, try removing the space between ISTEXT and (J12)

    TH,
    Maud

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •