Excel Help Needed

Can anyone tell me how to return a NULL value from a formula in an Excel Sheet? Either by standard formula or VBA.

Note - I'm not talking about returning a 0 value or an empty string ("") but a NULL value, as if the cell had never had anything entered into it. Any ideas?

Cheers

Neil

ISBLANK function any good?

for testing if a cell is NULL, yes. Not for setting a cell as NULL.

Interesting problem Neil, my initial feeling was that you were going to be out of luck, but I then came across this work around

Enter in to cell A1

=B1 C1 exactly as it is written i.e.

equals sign
B1
space
C1

Excel cant return anything for the "B1 space C1" part becaue it doesnt know what you are trying to do (is it range? is it a sum?) so it returns a null value which in turn gives you a #NULL error

Hope that makes sense

Unfortunately that doesn't work for me.

I'm formatting raw data, which could be up to 5000 rows in Sheet1, and outputting it to Sheet2 for export to CSV, but only want it to format lines that contain data, otherwise my CSV ends up with 2 lines of data and 4998 line of commas or #NULL!s

any suggestions (other than build a PHP app to do the job...)?

Neil -

This VBA seems to satisfy ISBLANK() :

Sub ZapA1()
Range("A1").Select
ActiveCell.FormulaR1C1 = ""
End Sub

(In A2 I'm using =IF(ISBLANK(A1),"BLANK","NOT BLANK") as a test)

That works in 2010

Import it to an Access table, make all fields "text" and then once loaded simply "copy-paste" it back into excel? Then reset your numeric fields and rewrite your formulas. It sounds complicated but it's a doddle compared to the rest of these solutions!!

Why not give Sean Blessitt a call at Astradyne Ltd, he's ya man!! www.astradyne.com

Neil,

I'm just about to do the school run, but I can sort this, no worries. Can we have a chat after 9:15?

Thanks to Nick and Alex for the mentions.

Cheers

Sean