Replace 1st empty cell as Null in excel

[vishalnegi]

I want if a table is empty then it should refect "Null" on the first cell i.e. A6 of the excel. Someone please help me with code for formula tool.

[RobertOdera]

Hi @vishalnegi 

It is better if you share a sample of what the input flow/file looks like versus a screenshot - cheers!

However, if I understand you correctly.

Are you reading in the file as input? This means that you would have F1, F2,....FN for column headers, right?

If that is the case:

1. Identify the target row and the row above it, which we assume will be the table header row using the Select Records Tool - from your screenshot this should be 5-6 (or you may select the entire range that depicts the table

2. Use the Dynamic Rename Tool --> formula = Take field names from first row of data

3. Use the Sample Tool --> First --> N=1 to return the first row of the table (especially if you selected the entire table range in step 1)

4. Use the Formula Tool or the Multi_Field Formula Tool if you need to treat many columns at the same time

5. The formula should read IF IsEmpty([Column Name]) THEN Null() ELSE [Column Name] ENDIF

IsEmpty evaluates for both nulls and blanks, so it is more robust, but you can also use IF IsNull...

I hope this helps. Cheers!