Can we check which Custom type is applied to the cell in Excel Sheet-CodePudding

I have an Excel sheet where 2 different Custom Type is applied for one column. How can I differentiate between those two? Is it possible using =IF? If so what condition do I need to use? I tried =TYPE(G7) but it gave Numeric for both. There is no formula for the cells.

CodePudding user response：

I don't think you can determine that with a Worksheet Function, but it is simple with a User Defined Function:

To enter this User Defined Function (UDF), alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like =NF(cell_reference) in some cell.

Function NF(rng As Range) As String
    Application.Volatile
    NF = rng.NumberFormat
End Function

This returns the cell format as a text string. So to determine whether it is a Cr, something like

is it a cr? =ISNUMBER(SEARCH("Cr",nf(E6)))

can be used as a logical test