- In set theory, the cardinality of the continuum is the cardinality or 'size' of the set of real numbers, sometimes called the continuum.It is an infinite cardinal number and is denoted by (lowercase fraktur 'c') or.
- News, email and search are just the beginning. Discover more every day. Find your yodel.
- A blocker corporation is a type of C Corporation in the United States that has been used by tax exempt individuals to protect their investments from taxation when they participate in private equity or with hedge funds.
- For federal income tax purposes, a C corporation is recognized as a separate taxpaying entity. A corporation conducts business, realizes net income or loss, pays taxes and distributes profits to shareholders. The profit of a corporation is taxed to the corporation when earned, and then is taxed to the shareholders when distributed as dividends.
Regular Expressions Cheat Sheet by DaveChild - Cheatography.com Created Date: 4237Z.
Regular expressions are ubiquitous in the developer world. They are used to validate website input, search for word patterns in large strings/texts and for many other uses. In Excel, Regular Expressions (VBA RegEx or simply VBA Regex) are not much advertised. Most users are good with using simple LEFT, RIGHT, MID and FIND functions for their string manipulation. These functions are, however, quite primitive and limited. Knowing how to use Regular Expressions (Regex) in Excel will save you a lot of time. This Excel Regex Tutorial focuses both on using Regex functions and in VBA. Let’s, however, not forget that VBA has also adopted the VBA Like operator which sometimes allows you to achieve some tasks reserved for Regular Expressions.
Excel Regex VBA example
A Regex (Regular Expression) is basically a pattern matching strings within other strings. Let’s demonstrate this with a simple Regex example. Let us assume we have the text below. And we want to capture just the numbers. Without knowing ahead how the text looks like it would be hard to extract these numbers both using Excel Functions and VBA. Below a simple example where we check if the pattern exists in the string.
This is the output result. It returns True as string pattern was found
What does the [0-9]+ pattern represent? It translates to the following: capture any pattern matching the following range of characters ([ ]), being numbers from 0-9, in a sequence of at least 1 or more (+). As you can see a Regex uses a certain code to translate your pattern.
Regular Expression Language
The Regular Expression language (Regex) is quite elaborate but allows you to match virtually any regular language. Below a quick reference:
Matching characters
| Syntax | Description | Example | Example match |
|---|---|---|---|
| . | Any character except newline (vbNewLine) | d.g | “dog” in “My dog is named dingo” |
| [characters] | Matches any provided character between brackets [ ] | [af] | “a” , “f” in “alfa” |
| [^characters] | Matches any character not being one of the provided between brackets [ ] | [^af] | “a” , “f” in “alfa” |
| [start–end] | Matches any character belonging to the character range specified between brackets [ ] | [0-9] | “1” and “2” in “12” |
| w | Any word character (letters, modifiers, digits, punctuation and connectors) | w | “I”, “a” “m” “J” “o” “h” “n” in “I_am.John” |
| W | Any non-word character | w | “_” and “.” in “I_am.John” |
| s | Any white space character | s | ” ” in “Hi There!” |
| S | Any non-white space character | S | “M” and “e” in “M e” |
| d | Any decimal digit | d | “1” and “2” in “12” |
| D | Any non-decimal digit | D | “d”, “_”, “.” in “d_.” |
| Followed by any special character – escapes special characters | . | “.” in “d.g” | |
| r | Tab (vbTab) | r | |
| n | Carriage return / new line (vbNewLine) | n |
Quantifiers
Quantifiers allow you to specify the amount of times a certain pattern is supposed to matched against a string. It is important to understand the difference between GREEDY and non-GREEDY quantifiers:
| Syntax | Description | Example | Example match |
|---|---|---|---|
| * | Zero or more of (GREEDY). Matches as many as possible | W.*W | “_dogs_cats_” in “_dogs_cats_” |
| + | One or more of (GREEDY). Matches as many as possible | Ww+W | “_dogs_cats_” in “_dogs_cats_” |
| ? | Zero or once (GREEDY). Matches as many as possible | d? | “1” in “Live1” |
| {n} | “n” many times | d{2} | “21” and “12” in “212” |
| {n,} | At least “n” times (GREEDY) | d{2,} | “12” and “123” in “1_12_123” |
| {n,m} | Between “n” and “m” times (GREEDY) | d{3,4} | “123” and “1234” in “1_12_123_1234” |
| *? | Zero or more of (non-GREEDY). Matches as few as possible | W.*?W | “_dogs_” and “_cats_” in “_dogs_cats_” |
| +? | One or more of (non-GREEDY). Matches as few as possible | W.+?W | “_dogs_” and “_cats_” in “_dogs_cats_” |
| ?? | Zero or once (non-GREEDY). Matches as few as possible | d?? | “1” in “Live1” |
| {n,}? | At least “n” times (non-GREEDY). Matches as few as possible | d{2,} | “12” and “123” in “1_12_123” |
| {n,m}? | Between “n” and “m” times (non-GREEDY). Matches as few as possible | d{3,4} | “123” and “1234” in “1_12_123_1234” |
Grouping
Below the basic grouping expressions:
| Syntax | Description | Example | Example match |
|---|---|---|---|
| (expression) | Group and capture the expression within the parenthesis ( ) | ([0-9]*) | Captures “123, “345” and “789” within “123-456-789” |
| (?:expression) | Group BUT DON’T CAPTURE the expression within the parenthesis ( ) | (?:[0-9]*)([A-Z]*)(?:[0-9]*) | Captures only “hello” in “123hello456” |
Using Regex in VBA
To use Regex in VBA you need to use the RegExp object which is defined in the Microsoft VBScript Regular Expressions library. To start using this object add the following reference to your VBA Project: Tools->References->Microsoft VBScript Regular Expressions. Otherwise, if you don’t want to reference this library every time you can also create the RegExp object using the CreateObject function.
Option 1: Referencing the library Microsoft VBScript Regular Expressions
Option 2: Using the CreateObject function
I personally prefer using the CreateObject function as it does not require referencing the library every time the Workbook is opened on a new workstation.
The RegExp object has the following properties:
- Pattern – The pattern (written in Regex) which you want to match against (e.g. “(.*)”)
- IgnoreCase – Ignore letter case (captial/non-capital letters)
- Global – Do you want to find all possible matches in the input string? If false, only match the first found pattern. Set false if you need just the first match for performance
- MultiLine – Do you want to match the pattern across line breaks?
The RegExp object facilitates the following 3 operations:
- Test (string) – returns True if the pattern can be matched agaist the provided string
- Replace (search-string, replace-string) – replaces occurrences of the pattern in search-string with replace-string
- Execute (search-string) – returns all matches of the pattern against the search-string
Regex: Test pattern against a string
The Test function allows you to test whether the selected Pattern provides any match against the string.
Regex: Replace pattern in a string
The Replace function will replace the first (if Global = False) or all matching patterns (if Global = True) within a certain string with another string of your choosing.
Regex: Match pattern in a string
The Execute function will match the first or all instances of a certain pattern within a certain string. You can also “capture” parts of the patterns as so called “Submatches”.
As you can see we have managed to capture 3 instances of the 123-[0-9]+ pattern in the string. We can also define a “capture” within our pattern to capture parts of the pattern by embracing them with brackets “()”. See the example below:
Regex: Using Regex as an Excel Formula
Excel does not natively provide any Regex functions which often requires creating complex formulas for extracting pieces of strings otherwise easy to extract using Regular Expressions. Hence, to facilitate Regex in Excel you need to use User Defined Functions – functions defined in VBA but accessible as regular functions in Excel. Below find 2 basic UDF functions created just for this use:
Now for an example:
…and the result:
Download Excel Regex example
You can download VBA Regex and other great snippets as part of the VBA Time Saver Kit. Feel free to download the full kit using the link below:
ReFiddle – Online testing your Regex!
Want to test quickly a Regular Expression (Regex)? Use ReFiddle. It is a great tool to quickly validate if a Regex works and to be able to quickly share your regex with others!
Keep in mind, however, that the VBA Regular Expression language (supported by RegExp object) does not support all Regular Expressions which are valid in ReFiddle.
Learn Regex (Regular Expression) the Fun way
Want to learn building Regex (Regular Expressions) and have some fun at the same time?
Try Regex Golf:
Regex Golf
| C4/C5 | |
|---|---|
| Overview | |
| Manufacturer | Ford Motor Company |
| Production | 1964–1986 |
| Body and chassis | |
| Class | 3-speed longitudinalautomatic transmission |
| Related | Ford C6 |
| Chronology | |
| Predecessor | Ford-O-Matic |
| Successor | AOD |
The Ford C4 is a three-speed, medium-duty automatic transmission introduced on 1964 model year vehicles and produced through 1981. The C4 was designed to be a lighter and more simple replacement for the original Ford-O-Matic two speed transmission being used in smaller, less powerful cars.
Ford used the term 'SelectShift' because in the first C4's, placing the gear selector in D2 forced the transmission to start in second gear and then shift to third gear. If the transmission was placed in D1, the transmission would start in first gear, then shift to second and third gear as normal. If the gear selector was placed into L, the transmission stayed in first gear only. The shifter display appeared as P-R-N-D2-D1-L. Because this was confusing, later versions of the C4 were changed to a P-R-N-D-2-1 (or L) pattern typically seen today.
Because of its cast iron construction, the Ford-O-Matic was very heavy. In designing the C4, Ford used an aluminum alloy, three-piece case (bell housing, main case, and tailhousing). The aluminum case and the use of a more simple Simpson planetary gearset reduced the weight significantly. It was primarily used with Ford's inline six-cylinder engines and small V8 engines (see Ford Windsor engines), usually up to 302 in³ (5.0 L). By comparison, the 351 Windsor and 351 Cleveland small and intermediate-block engines were backed by the medium-duty FMX or the heavy-duty C6 that debuted in 1966. Some C4s were built with a larger spread bell housing to use with 351M V8s, but these are rare. A few were also used with FE engines, mostly the 390. in full-size cars. Ratios are 2.46 low, 1.46 second and direct high.
The early model C4 (1964–1969) used a .788-inch 24-spline input shaft, which was upgraded in 1970 to 26-spline and .839-inch. The upgrade also included a matching clutch hub of 26-spline. In 1971, Ford went to a 26/24-spline input shaft, meaning the torque-converter side is 26-spline and the clutch hub is 24-spline.
The C4 was also found with valve bodies requiring a different number of bolts, 8-bolt vs 9-bolt. A 9-bolt valvebody can be used on either case, but a nut & bolt must be used on the valve body in the empty hole, dropping the bolt in from the top and using the nut on the bottom/filter side.
Modified C4s remain popular with hot rodders and drag racers due to their simplicity and durability.
Year & Model breakdown:
- 1964–1966 Select Shift, 24/24 spline, castings: C4, C5, C6
- 1967–1969 Select Shift, 24/24 spline, castings: C7, C8, C9
- 1970–1970 Select Shift, 26/26 spline, castings: D0
- 1971–1979 Select Shift, 26/24 spline, castings: D1, D2, D3, D4, D5, D6, D7, D8, D9
Applications:
- 1973–1977 Ford Bronco
- 1974–1982 Ford Cortina
- 1964-1967 Ford Econoline and Falcon Vans
- 1965–1983 Ford F-Series
- 1964–1970 Ford Fairlane
- 1978–1983 Ford Fairmont
- 1965–1970 Ford Falcon
- 1975–1982 Ford Granada
- 1975–1980 Ford LTD
- 1970–1977 Ford Maverick
- 1965–1981 Ford Mustang
- 1965–1979 Ford Ranchero
- 1968–1981 Ford Thunderbird
- 1968–1976 Ford Torino
- 1964–1981 Lincolns
- 1977–1980 Lincoln Versailles
- 1974–1980 Mercury Bobcat
- 1972–1981 Mercury Capri
- 1964–1977 Mercury Comet
- 1967–1981 Mercury Cougar
- 1975–1980 Mercury Monarch
- 1968–1976 Mercury Montego
- 1978–1981 Mercury Zephyr
C5[edit]
As fuel economy became more important in the 1970s, and 1980s, the C4 was replaced in 1982 by the C5, which was essentially a C4 with a lock-up clutch in the torque converter to improve highway fuel economy. It bore the casting numbers E2, E3, E4, E5, and E6, corresponding with the year it was produced. The C5 was phased out in 1986, replaced by the AOD. The production plant in Sharonville, Ohio was converted to production of the C6 transmission which was relocated from Livonia, Michigan, as the Livonia facility was converted to the AOD.
Applications:
Regex Guide
- 1986 Ford Aerostar
- 1983–1986 Ford Ranger
- 1983–1985 Ford Bronco II
- 1983–1986 Ford LTD
- 1982–1986 Ford Thunderbird
- 1982–1986 Mercury Capri
- 1982–1986 Mercury Cougar
- 1983–1986 Mercury Marquis
- 1983 Mercury Zephyr
How To Use Regex