Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Determining Font Formatting.

Determining Font Formatting

Written by Allen Wyatt (last updated April 13, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


2

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Generating a Web Page

Want your worksheets to be available to others on the Internet? Excel provides a way you can save your data in HTML ...

Discover More

Displaying a Column Number

Word allows you to format your document to use columns. If you want to number those columns for a printout, Word provides ...

Discover More

Widening Multiple Columns Proportionally

It is easy to adjust the width of columns in Excel. It is much harder to adjust the width of a range of columns ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Using Fractional Number Formats

If you want information to display on the screen using fractions instead of decimals, you're in luck. Excel provides ...

Discover More

Changing Cell Patterns

You can shade your cells by filling them with a pattern. Here's how to select the pattern you want used.

Discover More

Identifying Merged Cells

Merging cells is a common task when creating worksheets. Merged cells can play havoc with the normal functioning of some ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 7?

2024-04-26 11:17:55

J. Woolley

My Excel Toolbox includes the following dynamic array function:
    =ListCellFormat([Target])
This returns an array of format properties for Target's top-left cell. If Target is omitted, the formula's cell is assumed; otherwise, it can be a cell or range on any worksheet of an open workbook. Results correspond to tabs in the Format Cells dialog: Number, Alignment, Font, Border, Fill, Protection. Expect 2 columns and 24 rows plus 4 rows for each non-blank border line (edge or diagonal) plus 1 or 2 rows if the cell has a gradient fill effect.
See https://sites.google.com/view/MyExcelToolbox/


2024-04-13 16:27:57

J. Woolley

The old Excel 4 macro function GET.CELL can return a cell's font name and size. A formula with GET.CELL cannot be used in modern versions of Excel, but it is possible to use an Excel defined name that returns a GET.CELL result.

Click Formulas > Defined Names > Define Name (Alt+M+M+D) and enter
Name: FontNameCellLeftOne
Comment: Return font name for cell located one column to the left.
Refers To: =GET.CELL(18,INDIRECT("R[0]C[-1]",FALSE))

Repeat Formulas > Defined Names > Define Name (Alt+M+M+D) and enter
Name: FontSizeCellLeftTwo
Comment: Return font size for cell located two columns to the left.
Refers To: =GET.CELL(19,INDIRECT("R[0]C[-2]",FALSE))

If Oscar's text in cell A1 is in 12-pt Arial, the following formula in cell B1 will return Arial as text
    =FontNameCellLeftOne
and the following formula in cell C1 will return 12 as numeric
    =FontSizeCellLeftTwo

My Excel Toolbox includes the EvaluateExcel4Macro macro to evaluate and display the result of an Excel 4 macro function with an option to put the result into a cell. The EvaluateExcel4Macro macro accepts an Excel 4 macro function as text, but cell references must be in R1C1 style. Cell A1 of the active sheet is equivalent to !R1C1. See https://excelribbon.tips.net/T008803
Therefore, this Excel 4 macro function returns the font name for cell A1
    GET.CELL(18,!R1C1)
and this Excel 4 macro function returns the font size for cell A1
    GET.CELL(19,!R1C1)
See https://sites.google.com/view/MyExcelToolbox/
For more on Excel 4 macro functions, see
https://exceloffthegrid.com/using-excel-4-macro-functions/


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.