Skip to content
Archive of posts tagged formula

Excel corrupts certain workbooks in migrating from 2003 to 2007

I got a email from a client asking for help because Excel was “destroying,” to use his terminology, his 2003 workbook after conversion to the 2007 format. And, after analyzing the kind of change Excel made, I had to agree. The following in 2003 Figure 1 becomes, in 2013 (and in 2010), Figure 2 The […]

The Excel FORMULATEXT function

With Excel 2013, Microsoft introduced several new functions, one of which is worth a separate mention. FORMULATEXT displays as text the formula in a cell. For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0116%20formulatext%20function.shtml Tushar Mehta

Create a dynamic range reference

One of the most common issues that arise in Excel is that a range that contains data will eventually expand as more data are added or even contract as data are removed. If we have a formula, or an Excel functionality (such as Data Validation or a PivotTable), or a chart, that refers to such […]

Extract the last token in a cell

There are times when one wants to extract the last part of a string, say the file name from a string that contains the filename including the path. This short note describes a few ways to do that. The example we will use is the following. Cell A1 contains the value c:\x\yz.jpg. What we want […]