How to Test Cell Types in Excel

If you want to test the content of a cell in an Excel page this can be done by first extracting the content of the cell and then use a Test Values step to perform the actual test. This is essentially not different from what you would do in other page types, e.g. HTML, but what if you wanted to determine the cell type of a cell that would not be straightforward or even possible by just extracting the content of a cell and subsequently performing a test on this, e.g. there is no way to determine whether a cell is blank or contains an empty text. Fortunately Design Studio contains a step to perform such a test: the Test Cell Type step.

There are six different cell types that you can test for and these correspond directly to what you in Excel can test for with functions ISTEXT, ISNUMBER, etc.:

Blank:
This corresponds to the Excel function ISBLANK .
Text:
This corresponds to the Excel function ISTEXT .
Number:
This corresponds to the Excel function ISNUMBER . This type also includes dates since these are represented as numbers in Excel.
Logical:
This corresponds to the Excel function ISLOGICAL . This corresponds to the type Boolean in Design Studio .
Error:
This corresponds to the Excel function ISERROR .
Formula:
This corresponds to the Excel function ISFORMULA .

The Test Cell Type works like any other test step. It tests that the cell type in the found range matches a specified type and based on the result of this determines whether to continue along the branch or skip the following steps. The step is described in further details in the reference documentation on the Test Cell Type step.

An important property of the Test Cell Type step is that it can test the type of many steps simultaneously. As an example of this let us look at how one may test that an entire row is empty. This could be useful when looping over a document containing several structurally identical tables separated by blank lines. The figure below shows how to configure the step for this. In this example the branch following the step will be skipped, if the cells in the found range are all blank.

Example of Testing for Blank Cells

The figure below shows how to configure the Range Finder such that it finds an entire row. In this case we have a named range called "row" that has been set by a Loop in Excel step looping over rows and occurring before the Test Cell Type step. We have specified that the result should the entire row, by selecting "Whole of Range" for the Use property.

Range Finder Selecting an Entire Row