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.
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
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:
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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
When adjusting column width, Excel can add an extra line to some cells. This behavior seems to be related to the text ...
Discover MoreNeed to cram a bunch of text all on a single line in a cell? You can do it with one of the lesser-known settings in Excel.
Discover MoreWhen your macro checks the formatting used for a cell, it needs to be careful that the type of formatting being checked ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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/
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments