Determining Font Formatting

Oscar has a need to determine the font and font size applied to text in a cell. For instance, if the text in cell A1 is in 12-pt Arial, he would like a function that can be used to return "Arial" in cell B1 and 12 in cell C1.

There is nothing built-in to Excel that will allow this formatting information to be grabbed. You can, however, create a very simple macro that will do the trick. The following macro takes, as arguments, a cell reference and optionally an indicator of what data you want returned.

Function FontInfo1(Rn As Range, Optional iType As Integer = 1) As String
    Select Case iType
        Case 1
            FontInfo1 = Rn.Font.Name
        Case 2
            FontInfo1 = Rn.Font.Size
        Case Else
            FontInfo1 = "Info Not Specified"
    End Select
End Function

You use the function by using a formula such as this in a cell:

=FontInfo1(A1,1)

The second parameter (in this case 1) means that you want the font name. If you change the second parameter to 2 then the font size is returned. If you use some other value for the second parameter, then the function returns the text "Info Not Specified." If you leave the second parameter off entirely, then the function assumes you want the font name and returns that.

If you want to return both values at once, you can apply a lesser-known way of returning arrays of information from a user-defined function. Try the following:

Function FontInfo2(Rn As Range) As Variant
    FontInfo2 = Array(Rn.Font.Name, Rn.Font.Size)
End Function

To use this function, enter the following into a cell:

=FontInfo2(A1)

If you are using Excel 2021 or the version of Excel with Microsoft 365, then the array returned spills into to horizontal cells. (Meaning, if you enter the formula in cell C3, then you end up with values in cells C3:D3.) To use the function in an older version of Excel, just remember to select a range of two cells (such as C3:D3) and press Shift+Ctrl+Enter to enter the function as an array formula.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11358) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Determining Font Formatting.