The TRIMRANGE function removes empty rows and columns from the outer edges of a range of data. Given a range or array, it will exclude empty rows and/or columns and return a "trimmed" range that contains only data. The beauty of TRIMRANGE is that it will track the data in a worksheet as it...Read more
The DETECTLANGUAGE figures the language for a given text string. The result from DETECTLANGUAGE is a short language code indicating the language. For example, if the language is English, the result is "en"; if the language is French, the result is "fr"; if the language is Japanese, the...Read more
The TRANSLATE function translates text from one language to another in Excel. To perform a translation, TRANSLATE requires the text to translate and language codes to indicate the source and target language. The syntax for TRANSLATE looks like this:
=...Read moreA while back, I got from a reader about investing for his grandkids. The email asks the following question:
I hope you can assist me with an Excel calculation. I am keen to show my grandchildren the power of compound interest and the value of investing (patiently) for the...Read more
In this example, the goal is to look up the correct price of the product number entered in cell F4 using the product codes in column B. This problem is trickier than it looks. Each product code begins with 3 uppercase letters and ends with 2 or 3 uppercase letters. In the middle of the...Read more
After decades of waiting, Excel finally supports Regular Expressions, aka regex! Learn how three powerful new functions - REGEXTEST, REGEXREPLACE, and REGEXEXTRACT - can transform complex text operations into elegant, maintainable formulas. Whether you're cleaning data, validating...Read more
The IMAGE function is Excel's solution for adding images to a worksheet with a formula. IMAGE is similar in some ways to HTML's <img> tag, which is used to add images to an HTML page. Like the <img> tag, the IMAGE function allows you to set the image path and provide options for fit...Read more
Nearly 50 new functions have been added to Excel! This is not your Dad's Excel anymore – a lot has changed. This article takes a quick tour of the new functions, with links to more detailed information....Read more
The REGEXREPLACE function replaces text matching a specific regex pattern in a given text string. You can think of REGEXREPLACE as a much more powerful version of the simplistic SUBSTITUTE function. While both functions can be used to search and...Read more
In this example, the goal is to extract a date in a format like mm/dd/yy from a text string with a formula. The position of the date is not known, so the date must be located as a first step. This article explains two ways to solve this challenge: