+ Reply to Thread
Results 1 to 3 of 3

#VALUE! errror when text string not found

Hybrid View

BethHowe #VALUE! errror when text... 08-22-2008, 07:19 PM
daddylonglegs SEARCH returns either a... 08-22-2008, 07:35 PM
BethHowe Awesome! Thanks!! 08-22-2008, 07:47 PM
  1. #1
    Registered User
    Join Date
    08-22-2008
    Location
    Topanga, CA
    Posts
    2

    Question #VALUE! errror when text string not found

    How do I work around getting a #VALUE! error when the SEARCH function does not find the text string? I want to nest the SEARCH function in an IF statement, and the "if false" part won't work. Because the function returns the #VALUE! error instead of "FALSE" OR "0" I can't assign a zero as the value, if the text is not found. And because of this, I cannot SUM the results in a whole column, because although some values return what I assign for results that are TRUE, some of the values returned are errors.

    Example:
    =IF(SEARCH("/",H5,1) > 0, 1, 0)

    So if any slashes are found, I want a 1. If no slashes are found, I want a 0. Instead, I get a 1 or #VALUE!, which screws up my whole grand total.

    I'd really appreciate a work around. Thanks!!!!!!

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,721
    SEARCH returns either a number or an error so you can wrap it [or FIND] in an ISNUMBER function, i.e.

    =ISNUMBER(FIND("/",H5))+0

    or you could use COUNTIF

    =IF(COUNTIF(H5,"*/*"),1,0)

  3. #3
    Registered User
    Join Date
    08-22-2008
    Location
    Topanga, CA
    Posts
    2

    Smile Awesome! Thanks!!

    I knew I'd find super sleuths here! Wish they'd put those kind of solutions in the MS Help file. Thanks again!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 02-06-2012, 06:53 PM
  2. Converting ddmmyy date format to text string
    By Fos605 in forum Excel General
    Replies: 1
    Last Post: 06-06-2008, 09:49 AM
  3. Select and paste part of a text string ?
    By richardeallen in forum Excel General
    Replies: 3
    Last Post: 05-16-2008, 10:34 AM
  4. Extract Text From String
    By RobynC in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-13-2007, 09:53 AM
  5. Convert cell reference to text string
    By packe in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-08-2007, 09:56 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1