Learning Journey
now browsing by category
Pattern Matching in Power Query
In Power Query there is no tool yet for matching regular expressions (patterns).
Only in Power BI we can run scripts in R and Python, hopefully these languages will be added to Excel Power Query.
Until then, to solve this problem, with a little imagination, we can design our own pattern matching process.
Here is a file you can download and test:
Read MoreLEN function: Basic and Advanced Examples
LEN function type: Text
Description: Returns the number of characters in a text string
LEN is a basic function widely used, many formulas built for text processing will need this function.
Take a look at the advanced applications examples:
Applications:
- Get the text with the lowest length from a column (first match only)
- Count how many cells within a range of cells have a text length higher than normal
SUBSTITUTE function: Basic and Advanced Examples
SUBSTITUTE function type: Text
Description: Substitutes the old text with new text in a text string
Beyond its basic use, which is fairly simple, the SUBSTITUTE function can be very useful combined with other functions, to get your desired results.
Take a look at the advanced applications examples:
Applications:
- Substitute last occurrence only
- Extract partial string after last delimiter
- Extract item from delimited text, based on item position
Working with Excel Functions and Formulas
In this article on Excel Functions and Formulas :
1. Excel Intellisense: Formula AutoComplete
2. Working with Insert Excel Functions Wizard
3. Debug Complex Formulas: The Magic F9 and Ctrl+Z
4. Display items using display triggers
Working With a Defined Name
What is a Defined Name?
In Excel, you have the option to assign a Name to a cell, or to a Range of cells. This name is reffered to as a Defined Name.
A Defined Name will give more flexibility than before, you can refer to it in your formulas, or chart series.
Also, the name can be located anywhere in your workbook, you don’t need to use cell or range references.
Selecting Excel Table Components
Selecting Excel Table Components is slightly different from selecting normal ranges in a worksheet, because we have 3 Excel Table Components which are special ranges: Read More
Order of Precedence
The Order of Precedence used by Excel to calculate formulas is extremely important to know when building complex formulas.
Of course, Excel will respect all mathematical rules you learned in school, there are only a few more Operators in Excel: the Range Operators, single Space Operator, Union Operator, the Concatenation Operator and the Exponentiation Operator.
Excel Table Components
What is a Defined Table?
An Excel Defined Table is a special object in Excel which adds a lot of new options and functionalities when working with data ranges. With Defined Tables, you can manipulate data more easily than before, adding power and flexibility to your applications.
To create a Defined Table, select the data range, or simply select any cell within the data range, and click the Table button from the Insert tab from Ribbon. The keyboard shortcut key is Ctrl+T to create a Defined Table.
Excel Reference Operators
There are 3 Reference Operators in Excel:
- “:” (colon) is the Range operator, which produces one reference to all the cells between two cells references, including the cells written in the reference text, like: A11:A13
- “,” (comma) is the Union operator, which combines multiple Ranges into one reference, like A11:A13 , A11:C11 , A11 (you can refer to simple cells too, not only to ranges with multiple cells)
- ” ” (space) is the Intersection operator, which returns a reference to the cell or to the range of cells found at the intersection of the ranges. In this example, only cell A11 is found in both ranges: A11:A13 A11:C11
Excel Cell Modes
How can the Cell Modes affect your work?
When you try to edit a Conditional Formatting formula, and you click the left or right arrows to move across formula’s text, Excel inserts unwanted cell references. Same thing happens when you try to create or edit a Defined Name formula, or a Chart series…
If you are not aware of the active Cell Mode, then working with formulas, defined names formulas or conditional formatting formulas, editing chart series can become very annoying.