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
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
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
@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?
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.
