Empty to #N/A

[henrygeorge]

Hi Team

How do I replace any empty cell in my xlsx output to an #N/A. I tried using an iff statement doesn't seem to work cause it's a date data type I guess

[RolandSchubert · Accepted solution]

Hi @henrygeorge ,

I think, you'll have to convert data type to string (a string data type in general). A Multi-Field Formula tool could be useful, you can change to datatype and replace empty fields using a formula like

IF IsEmpty([_CurrentField_]) THEN
'#N/A'
ELSE
[_CurrentField_]
ENDIF

But all numeric data will be changed to string as well, resulting in formatting issues in Excel (green triangle in cells).

What do you think?

Best,

Roland 

[DiegoParker]

Hi @henrygeorge 

It depends what an "empty cell" is. Are they nulls or white spaces/blanks? 

So you can do a couple of things depending of the answer.

1) if isnull([Field]) then "N/A" else [Field] endif ->Null

2) If [Field]="  " then "N/A" else [Field] endif   ->Whitespace

3) If [Field]=" " then "N/A" else [Field] endif ->Blank

Hope this helps If does, can I ask you to mark it as a solution? this will help other users to find it and will allow us to close the thread. Many thanks!

Best,
Diego

[henrygeorge]

@RolandSchubert @DiegoParker  thanks for the reply. Another question so when I want to read the file again.. I want it to remove the #N/A to make them empty.. As I get an error that #N/A is not a valid date. To change this I changed the data type to v string and then put a formula tool - if the col = #N/A then "" else col Endif. But I get an error "" is not a valid date. What should I do? 

[DiegoParker]

@henrygeorge You need to make sure the output and input of your formula tool are strings. However, what I'd advise you to do is to have the empty dates as NULLS instead because otherwise you won't be able to keep the date format necessary for date calculations and will have to sue string instead. 

Best,

Diego

[DanielG]

You could also data cleanse ahead of the formula to remove all extra/duplicate whitespace and standardize those types of data points so your formula can be simpler.