Replace empty string with null

Hi all,

I'm working with some data exported from a database. Unfortunately, when data is exported to excel, cells that should be completely blank (i.e. ISBLANK formula would return TRUE) actually contain empty strings.

What is the fastest way to replace the empty string with a true null?

Thanks

When I say empty string I mean a cell with that appears blank/null but actually has an empty string in i.e ""

For example if you type the following formula into cell B1:

=IF(A1=1,"Yes","")

If cell A1 does not equal 1 the formula will return "" which is an empty string. Excel treats a cell containing an empty string differently to a cell which is actually blank/null.
The problem I have is the system I have exported data from instead of creating blank cells creates cells with empty strings in At present I know two ways of changing the cells with empty string to true null/blank cells:
1. Select each cell and click delete/clear contents
2. Use text to columns and set the column format as general.

Both of these methods are time consuming, particularly considering the quantity of data in question. I was hoping there might be a more efficient method?

i have populated a big table from excel, the excel has empty cells i have replace them with ""( empty string) with an if condition now again ihave to read from that table and send it to another table using VB.NET. i get an error "dbnull".....
can anyone anyone please helps

Originally Posted by Emily12

i have populated a big table from excel, the excel has empty cells i have replace them with ""( empty string) with an if condition now again ihave to read from that table and send it to another table using VB.NET. i get an error "dbnull".....
can anyone anyone please helps

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

anteagles20, I think XOR LX has an appropriate solution.

However, let me clarify one thing about your description. Your original post said you were importing from a database. That seems to preclude formulas, so I considered only constant values in cells. Your further explanation talked only about the result of formulas, a different matter. When you talk about how "Excel treats a cell containing an empty string" you are actually talking about how Excel treats a cell containing a formula that returns an empty string. The definition of ISBLANK is to return TRUE if the cell is empty; a cell with a formula is not empty, even if the value of that cell is the empty string.

Thanks XOR, i have used your code (with the removal of the select statement).

6StringJazzer: I meant the formula only as an example to illustrate how you may end up with a cell containing a null string. As stated in the original post the cells do not contain formulas, just null strings. Thanks for your input.

Thanks again