Using other features to identify linked data types

Formulas

You can always write formulas that reference data types. However, if you want to extract the text of a cell with a linked data type by using the TEXT function, you'll get a #VALUE! error.

A workaround is to use the FIELDVALUE function and specify the Name field for the field_name argument. In the following example, if cell A1 contained a stock data type, then the formula would return the stock name.

=FIELDVALUE(A1,"Name")

However, if cell A1 doesn't contain a linked data type, then the FIELDVALUE function will return a #FIELD! error. If you want to evaluate whether or not a cell contains a linked data type, you can use the following formula, which uses the ISERROR function to test if the FIELDVALUE function will return an error.

=IF(ISERROR(FIELDVALUE(A2,"Name")),"This cell doesn’t have a linked data type","This cell has a linked data type")

If the formula evaluates to an error, then it will return the text "This cell doesn’t have a linked data type", otherwise, it will return "This cell has a linked data type".

If you simply want to suppress the #FIELD! error, you can use:

=IFERROR(FIELDVALUE(A1,"Name"),"")

Which will return a blank cell if there's an error.

Conditional formatting

You can conditionally format a cell based on whether or not it has a linked data type. You'd first select the cells that need the conditional formatting, and then go to Home > Conditional Formatting > New Rule > Use a formula... For the formula, you'd use the following:

=NOT(ISERROR(FIELDVALUE(A1,"Name")))

Where cell A1 is the top cell in the range you want to evaluate. Then apply the format you want.

In this example, if cell A1 contains a valid field name for "Name", then the formula returns TRUE and formatting will be applied. If cell A1 doesn't contain a linked data type, then the formula returns FALSE, and no formatting will be applied. You could remove the NOT if you wanted to highlight any cells that don't contain valid linked data types instead.

VBA

There are several VBA (Visual Basic for Applications) methods that you can use to identify if a cell or range contains linked data types. This first procedure uses the HasRichDataType property. 

Both of these procedures will prompt you to select a range of cells to evaluate, then return a message box with the results.

Sub IsLinkedDataType()
    Dim c As Range
    Dim rng As Range
    Dim strResults As String
    
    Set rng = Application.InputBox("Select a range to check for linked data types", Type:=8)
    
    For Each c In rng
      '    Check if the HasRichDataType is TRUE or FALSE
        If c.HasRichDataType = True Then
        '   The cell holds a linked data type
            strResults = strResults & c.Text & " - Linked data type" & vbCrLf
        Else
            strResults = strResults & c.Text & " - Not a linked data type" & vbCrLf
        End If
    Next c

    MsgBox "Your range contains the following details" & vbCrLf & vbCrLf & strResults, vbInformation + vbOKOnly, "Results"
    
End Sub

This next procedure uses the LinkedDataTypeState property.

Sub IsLinkedDataTypeState()
    Dim c As Range
    Dim rng As Range
    Dim strResults As String
    
    Set rng = Application.InputBox("Select a range to check for linked data types", Type:=8)
    
    For Each c In rng
   '    Check if the LinkedDataTypeState is 1 (TRUE) or 0 (FALSE)
        If c.LinkedDataTypeState = 1 Then
        '   The cell holds a linked data type
            strResults = strResults & c.Text & " - Linked data type" & vbCrLf
        Else
            strResults = strResults & c.Text & " - Not a linked data type" & vbCrLf
        End If
    Next c
    
   MsgBox "Your range contains the following details" & vbCrLf & vbCrLf & strResults, vbInformation + vbOKOnly, "Results"

End Sub

This final code snippet is a user defined function (UDF), and you reference it just like any other Excel formula. Simply enter =fn_IsLinkedDataType(A1), where A1 is the cell you want to evaluate.

Public Function fn_IsLinkedDataType(c As Range)
'   Function will return TRUE if a referenced cell contains a linked data type
    If c.HasRichDataType = True Then
      fn_IsLinkedDataType = "Linked data type"
    Else
        fn_IsLinkedDataType = "Not a linked data type"
    End If
End Function

To use any of these examples, press Alt+F11 to open the Visual Basic Editor (VBE), then go to Insert > Module, and paste the code in the new window that opens on the right. You can use Alt+Q to exit back to Excel when you're done. To run either of the first two examples, go to the Developer tab> Code > Macros > select the macro you want to run from the list, then select Run.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.