+ Reply to Thread
Results 1 to 3 of 3

Display blank cell when formula returns non-numeric value. Please HELP

  1. #1
    Registered User
    Join Date
    08-16-2024
    Location
    Dallas Texas
    MS-Off Ver
    Microsoft 365 Excel Version 2406
    Posts
    12

    Question Display blank cell when formula returns non-numeric value. Please HELP

    Disclaimer: I know enough excel to be dangerous, now I need help with "compound formulas"

    The below formula is what I am working with:

    =IF(MasterTable!BV2="","",IF(MasterTable!BW2="",TEXT(MasterTable!BV2,"(###) ###-####"),TEXT(MasterTable!BV2,"(###) ###-####"&" x"&MasterTable!BW2)))

    This formula pulls and concatenates phone number cell and extension cell from master table into a "reorg" worksheet. As long as the master fields contain numeric values, then everything displays properly. However, as soon as a "genius" types text characters in a field it displays as "#VALUE!". what I need is if ALPHA characters exist in these phone number fields, to display blank in the destination cell.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,330

    Re: Display blank cell when formula returns non-numeric value. Please HELP

    Just use IFERROR to catch that error and show a blank:

    =IFERROR(IF(MasterTable!BV2="","",IF(MasterTable!BW2="",TEXT(MasterTable!BV2,"(###) ###-####"),TEXT(MasterTable!BV2,"(###) ###-####"&" x"&MasterTable!BW2))),"")
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    08-16-2024
    Location
    Dallas Texas
    MS-Off Ver
    Microsoft 365 Excel Version 2406
    Posts
    12

    Re: Display blank cell when formula returns non-numeric value. Please HELP

    I feel sheepish now!. Thank you for your help!!

+ 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: 3
    Last Post: 08-24-2021, 11:13 AM
  2. [SOLVED] Need help making COUNTIFS formula display as blank if it's 'criteria cell' is blank
    By 27POP27 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2018, 07:38 AM
  3. formula always returns #VALUE! when reference cell is blank
    By delboy007 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-21-2018, 03:54 AM
  4. [SOLVED] formula returns value even when reference cell is blank
    By opusarlo in forum Excel General
    Replies: 7
    Last Post: 04-24-2015, 06:55 AM
  5. [SOLVED] When my formula encounters a blank cell it returns a 0, I need it to return a blank
    By stretch99 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-27-2014, 07:52 PM
  6. Replies: 11
    Last Post: 02-01-2013, 04:48 PM
  7. Find first cell with formula that returns blank
    By daveIrwin in forum Excel General
    Replies: 1
    Last Post: 02-04-2005, 12:56 PM

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