If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




differentiating between formulas and values
I have a bunch of values in column A. In column B, I'm using a userdefined function that processes the values in column A and returns either text of some sort or a blank. However, even though the cells in column B appear to be unique  well, except for the blank cells  the formula in each cell is essentially identical (e.g. B1 is =UDF(A1), B2 is =UDF(A2) etc.). I want to do an operation only on those cells in column B that are displaying text, but I don't know how to do that automatically. Is there a way to somehow differentiate between cells that return a "tangible" value, and those that return a "blank" value?  shellshock  shellshock's Profile: http://www.excelforum.com/member.php...o&userid=24935 View this thread: http://www.excelforum.com/showthread...hreadid=385124 
Ads 
#2




One way that should work ...
If you use Auto Filter to filter out the Blanks, then you can select your range in ColB and use Select Special Visible Cells Only and then operate on the Tangible cells. Rgds, ScottO "shellshock" > wrote in message ...   I have a bunch of values in column A. In column B, I'm using a  userdefined function that processes the values in column A and returns  either text of some sort or a blank.   However, even though the cells in column B appear to be unique  well,  except for the blank cells  the formula in each cell is essentially  identical (e.g. B1 is =UDF(A1), B2 is =UDF(A2) etc.).   I want to do an operation only on those cells in column B that are  displaying text, but I don't know how to do that automatically.   Is there a way to somehow differentiate between cells that return a  "tangible" value, and those that return a "blank" value?      shellshock    shellshock's Profile: http://www.excelforum.com/member.php...o&userid=24935  View this thread: http://www.excelforum.com/showthread...hreadid=385124  
#3




Shell,
In Excel, there isn't any Tangible property, and we get together once a week and communally lament that there is no NULL value that can be returned from a formula or function. But you can test for the UDF returning text with: =ISTEXT(A2) Or look for a zerolength string with =LEN(A2)=0 =IF(LEN(A2)>0, "There's a string", "Empty string") Or look for a number: =ISNUMBER(A2)  Earl Kiosterud www.smokeylake.com/  "shellshock" > wrote in message ... > > I have a bunch of values in column A. In column B, I'm using a > userdefined function that processes the values in column A and returns > either text of some sort or a blank. > > However, even though the cells in column B appear to be unique  well, > except for the blank cells  the formula in each cell is essentially > identical (e.g. B1 is =UDF(A1), B2 is =UDF(A2) etc.). > > I want to do an operation only on those cells in column B that are > displaying text, but I don't know how to do that automatically. > > Is there a way to somehow differentiate between cells that return a > "tangible" value, and those that return a "blank" value? > > >  > shellshock >  > shellshock's Profile: > http://www.excelforum.com/member.php...o&userid=24935 > View this thread: http://www.excelforum.com/showthread...hreadid=385124 > 
#4




Scott, that's a neat idea with the autofilter, although I would need to write a macro in order for that to happen automatically. (My file is updated on an ongoing basis by several people, and I wouldn't want to have to do that operation manually.) Earl, I didn't even know about the LEN function until just now  and I think I was only vaguely aware of the ISTEXT function. In the end, I did something very much along those lines: (=IF(B1<>"", *my operation*, "")) Thanks very much to both of you.  shellshock  shellshock's Profile: http://www.excelforum.com/member.php...o&userid=24935 View this thread: http://www.excelforum.com/showthread...hreadid=385124 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
#N/A Values : Returned by Formulas vs Entered Manually  monir  Charts and Charting in Excel  8  July 7th 05 01:16 AM 
How do I sum rounded values from two seperate formulas in Excel?  Federali  Excel Worksheet Functions  1  May 3rd 05 06:01 PM 
How do I clear a worksheet of values, but leave formulas intact?  [email protected]  Excel Worksheet Functions  2  April 18th 05 05:53 PM 
display values of formulas in Excel  Binyaaust  Excel Worksheet Functions  1  April 14th 05 12:35 PM 
delete values in several cells without deleting the formulas  dranreb  Excel Discussion (Misc queries)  4  December 9th 04 01:15 AM 