The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,854 other subscribers
«

CELL function looks interesting but beware: language nightmares coming up…

Posted by jpluimers on 2026/03/26

A while ago I bumped into a very promising [Wayback/Archive] CELL function – Microsoft Support which exposes all sorts of interesting information on an Excel WorkSheet cell including address and filename.

But then this “disclaimer” threw me off:

Note: Formulas that use CELL have language-specific argument values and will return errors if calculated using a different language version of Excel. For example, if you create a formula containing CELL while using the Czech version of Excel, that formula will return an error if the workbook is opened using the French version.  If it is important for others to open your workbook using different language versions of Excel, consider either using alternative functions or allowing others to save local copies in which they revise the CELL arguments to match their language.

It means the CELL function is only useful if the spreadsheet containing it will only ever be used in a single language: say goodbye to portability.

That’s a real bummer as it would have simplified formulas like =ADDRESS(ROW(E7), COLUMN(E7)) into =CELL("address", E7) both resulting $E$7.

The big problem is that “consider either using alternative functions” is hardly possible as many of the functions have no alternative, for instance using the CELL function is the only way to get the name of the current worksheet (prepended by the filename) as =CELL("filename") returns Macintosh HD:Users:jeroenp:Downloads:[Workbook1.xlsx]Sheet1.

Note however:

Filename (including full path) of the file that contains reference, as text. Returns empty text (“”) if the worksheet that contains reference has not yet been saved.

The basic syntax of it is CELL(info_type, [reference]), where info_type and some of the return values being language dependent:

en-US English – United States nl-NL Dutch – Netherlands de-DE German – Germany fr-FR French – France ja-JP Japanese – Japan
"address" "adres" "Adresse" "adresse" "address"
"col" "kolom" "Spalte" "col" "col"
"color" "kleur" "Farbe" "couleur" "color"
"contents" "inhoud" "Inhalt" "contenu" "contents"
"filename" "bestandsnaam" "Dateiname" "nomfichier" "filename"
"format" "notatie" "Format" "format" "format"
"format" values "-", "()" "notatie" waarden "-", "()" "Format" Textwerte "-", "()" "format" valeurs "-", "()" "format" 値 "-", "()"
"parentheses" "haakjes" "Klammern" "parentheses" "parentheses"
"prefix" "voorvoegsel" "Präfix" "prefixe" "prefix"
"prefix" values "'", """, "^", "\", "" "voorvoegsel" waarden "'", """, "^", "\", "" "Präfix" Textwerte "'", """, "^", "\", "" "prefixe" valeurs "'", """, "^", "\", "" "prefix" 値 "'", """, "^", "¥", ""
"protect" "bescherming" "Schutz" "protege" "protect"
"row" "rij" "Zeile" "ligne" "row"
"type" "type" "Typ" "type" "type"
"type" values "b", "l", "v" "type" waarden "g", "l", "w" "Typ" Textwerte "b", "l", "w" "type" valeurs "i", "l" , "v" "type" 値 "b", "l", "v"
"width" "breedte" "Breite" "largeur" "width"

Note I checked only the documentation BCP 47 locale codes indicated in the table (which in the below URLs, contrary to the specifications, are lowercase like en-us).

CELL(...) examples

"filename"

I originally bumped into the CELL function when searching for a way to get the WorkSheet name of a reference.

That’s what the "filename" functionality is for which I found in the first search result from [Wayback/Archive] excel get worksheet name from reference – Google Search.

[Wayback/Archive] Get sheet name only – Excel formula | Exceljet showed these solutions:

=TEXTAFTER(CELL("filename",A1),"]")

…

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

I think the above should consider that "]" are allowed in filenames too. Need to check that out later, but for now that was enough knowledge.

Take Microsoft examples for "filename" with a grain of salt as some are both targeted for Windows and fail both on Windows and MacOS:

  • [Wayback/Archive] Insert the current Excel file name, path, or worksheet in a cell – Microsoft Support
    1. display the current file name with its full path and the name of the current worksheet:
      =CELL("filename")
    2. to display the current file name and active worksheet name:
      =RIGHT(CELL("filename"),LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"), ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))
    3. the name of the current file in a cell:
      =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

    The first functions OK, but the second fails as these are the return values on MacOS and Windows for:

    1. =CELL("filename)"
      1. Macintosh HD:Users:jeroenp:Downloads:[Workbook1.xlsx]Sheet1
      2. C:\Users\jeroenp\Downloads\[Book1.xlsx]Sheet1
    2. the full formula:
      1. Macintosh HD:Users:jeroenp:Downloads:[Workbook1.xlsx]Sheet1
      2. Users\jeroenp\Downloads\[Book1.xlsx]Sheet1

    I suspect the third one will fail when [ or/and ] are used as part of the filename as well.

"address"

Most people use the "address" functionality of the CELL function as contrary to the ADDRESS function, it requires only one input reference.

If you can trim your usage of Excel to one locale, then these examples will help you with that

"contents"

Note there is also the "contents":

"contents" Value of the upper-left cell in reference; not a formula.
  • [Wayback/Archive] Combining address function and cell function in Excel – Super User (thanks [Wayback/Archive] hossayni and [Wayback/Archive] Andi Mohr)

    Q

    In MS Excel 2013, when I write
    =CELL("contents",$B$1)
    It is okay, but when I write
    =CELL("contents",ADDRESS(1,2))
    It results in error.

    A

    When you enter =CELL("contents",ADDRESS(1,2)) you are expecting Excel to evaluate that expression to =CELL("contents",$B$1).
    This isn’t quite what Excel does. Excel will read it as =CELL("contents","$B$1"). Note the quotes – Excel thinks this is a string, not a cell reference. This is why you get an error.
    To convert the string output of ADDRESS() to a cell ref you can adjust your formula slightly like this:
    =CELL("contents",INDIRECT(ADDRESS(1,2)))
    The INDIRECT() function simply converts your string into a real cell reference Excel can use.

Is the localisation a real problem?

Since the CELL function was introduced way after Microsoft knew about localisation issues, I expected it to be neutral to localisation, but it wasn’t, hence I wrote this Twitter [Wayback/Archive] Thread by @jpluimers on Thread Reader App starting with [Wayback/Archive] Jeroen Wiert Pluimers @wiert@mastodon.social on Twitter: “@applescripter @Felienne @ICER_C Bumped into another Excel feature today that has this: the CELL function which depends on which locale of Office you have installed. support.microsoft.com/en-us/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf“

=IF(CELL("type",A1)="v",A1*2,0) This formula calculates A1*2 only if cell A1 contains a numeric value, and returns 0 if A1 contains text or is blank. Note: Formulas that use CELL have language-specific argument values and will return errors if calculated using a different language version of Excel. For example, if you create a formula containing CELL while using the Czech version of Excel, that formula will return an error if the workbook is opened using the French version. If it is important for others to open your workbook using different language versions of Excel, consider either using alternative functions or allowing others to save local copies in which they revise the CELL arguments to match their language.=IF(CELL("type",A1)="v",A1*2,0)

This formula calculates A1*2 only if cell A1 contains a numeric value, and returns 0 if A1 contains text or is blank.

Note: Formulas that use CELL have language-specific argument values and will return errors if calculated using a different language version of Excel. For example, if you create a formula containing CELL while using the Czech version of Excel, that formula will return an error if the workbook is opened using the French version. If it is important for others to open your workbook using different language versions of Excel, consider either using alternative functions or allowing others to save local copies in which they revise the CELL arguments to match their language.

Contrasting the above documentation, the English argument values might work for other locales as per [Wayback/Archive] worksheet function – Is there a good way to use Excel CELL() formula without prior knowledge of the locale the file will be used in? – Super User (thanks [Wayback/Archive] I’m with Monica and [Wayback/Archive] teylyn):

Q

Is there a way to produce a formula that will work in any and all Excel localizations? Preferrably without trying out all 35+(?) localization language parameter strings for each CELL() function call using cascaded IFERROR-blocks?
Initially I had hoped that the string parameters were just keys to some internal enumeration, and that knowing the actual values behind the keys and passing them instead of the supposed key sting to work. But the function actually requires exactly one of the allowed – localized – strings.

A

If the first argument of the Cell function is in English, then it should work in all locales.
If you want to test this on the same computer, it is not sufficient to change just the language pack, but you must also change the Windows settings for locale, and then – for good measure – even give it a reboot before opening the file using the other language and locale.

In addition, it looks like there is a translation tool that plugs into the Windows version of Excel. I have not tried it yet, as I mainly use MacOS for Microsoft Office, but these links look promising:

Address operations are slow!

Reminder that =CELL("address", ...) is as slow as an =ADDRESS(...) equivalent. And the latter is slow as per [Wayback/Archive] Alternative to INDIRECT() in Excel – Stack Overflow (thanks [Wayback/Archive] ytoamn):

Q

I am currently using a formula =MATCH(A2,INDIRECT(Q20):INDIRECT(Q22),0) where A2 contains an item to be searched in a range. The range is dynamic, Q20 holds the start point as =ADDRESS(Q17,3,4) and Q22 holds =ADDRESS(Q18,3,4). Q17 and Q18 contain a formula to calculate the desired row number based on user input in a specific cell B2. As you can see, the range is generated dynamically.
Can anyone suggest doing the same using a non-volatile function ?
This is to be used in multiple places and it slows down the entire spreadsheet if done so. I also want to keep Manual Calculation disabled as I need to return updated results as soon as a the input is entered in the specific cell B2.

A

INDEX can usually replace INDIRECT and ADDRESS. It is also considered non-volatile (unlike INDIRECT and ADDRESS) so it will not recalculate as much.
 =MATCH(A12, INDEX(A:Z, Q17, 3):INDEX(A:Z, Q18, 3), 0)
 'if always in column C then the range can be tightened.
 =MATCH(A12, INDEX(C:C, Q17):INDEX(C:C, Q18), 0)

References

Related:

The easiest way to demo in Excel as of 2013 is to use the FORMULATEXT function which was introduced on Windows in Excel 2013 as part of Office 2013 and on MacOS in 2015 Excel 15.0 as part of Office 2016 for Mac as part of adding a truckload of new functions:

These were of no use to get WorkSheet names but might still be interesting for understanding Excel concepts like 3D references:

Queries

There are quite a few because it is hard to query about obtaining Excel meta information when so much of their function names are named after the concepts used.

–jeroen

This entry was posted on 2026/03/26 at 12:00 and is filed under Development, Excel, Office, Office Development, Power User, Software Development. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

«