 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2008Well, how empty is it?
Is it possible, without VBA, to test the “emptiness” of a cell? For example, a formula that will return 1 if a cell is unused (has nothing in it), 2 if the cell has a formula returning a null string: =IF(1=1,””,””) 3 if the cell has a single quote in it and 4 otherwise?  SignatureGary''s Student - gsnu2007xx >Is it possible, without VBA, to test the emptiness of a cell? For example, >a formula that will return 1 if a cell is unused (has nothing in it), 2 if [quoted text clipped - 3 lines] > >3 if the cell has a single quote in it and 4 otherwise? =IF(ISBLANK(A1),1,IF(LEN(A1)=0,2,IF(NOT(ISERR(FIND("'",A1))),3,4))) Of course, this is testing for the presence of a single quote -- NOT for the use of a single quote to tell Excel to format the rest of the entry as TEXT. --ron >Is it possible, without VBA, to test the emptiness of a cell? For example, >a formula that will return 1 if a cell is unused (has nothing in it), 2 if [quoted text clipped - 3 lines] > >3 if the cell has a single quote in it and 4 otherwise? Something like: =IF(ISBLANK(A1),1,IF(AND(LEN(A1)=0,CELL("prefix",A1)="'"),3,IF(LEN(A1)=0,2,4))) detects if the single quote is present by itself and as a prefix character. But you really didn't specify that. --ron Excellent!!! This is exactly what I need! SignatureGary''s Student - gsnu200767 > >Is it possible, without VBA, to test the “emptiness” of a cell? For example, > >a formula that will return 1 if a cell is unused (has nothing in it), 2 if [quoted text clipped - 12 lines] > But you really didn't specify that. > --ron >Excellent!!! > >This is exactly what I need! Glad to help. Thanks for the feedback. --ron try this logic to get to the last one =trim(a1) =len(trim(a1)) =if(len(trim(a1))<1,1,2) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Is it possible, without VBA, to test the “emptiness” of a cell? For > example, [quoted text clipped - 4 lines] > > 3 if the cell has a single quote in it and 4 otherwise? thanks SignatureGary''s Student - gsnu200768 > try this logic to get to the last one > =trim(a1) [quoted text clipped - 9 lines] > > > > 3 if the cell has a single quote in it and 4 otherwise? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.