Partial Match with Numbers and Wildcard: How It Works

This article explains how partial matching works when numbers are involved and a wildcard character (such as * or ?) is used. You will learn what a partial match is, how wildcards interact with numeric data, common pitfalls, and how to apply this in practical scenarios like spreadsheet lookups or database queries.

Formula

The core idea of a partial match is that you compare a search term against a data value and consider it a match if the search term appears as a substring, not necessarily equal to the whole value. When numbers are involved, the formula is usually expressed as:

Match = (search term is a substring of data value)

For text, this is straightforward. For numbers, you first convert the number to a string (e.g., 123 becomes “123”), then apply the substring check. A wildcard character, typically * (any sequence) or ? (any single character), extends this by allowing you to specify a pattern. The pattern is converted to a regular expression, and the match is true if the regular expression finds a match anywhere in the string.

Worked Example

Using the calculator above, suppose you have a total time of 90 minutes and a distance of 15 km. The average pace is calculated as 90 / 15 = 6.0 min/km. The average speed is 15 / (90 / 60) = 15 / 1.5 = 10.0 km/h. These are exact calculations, not partial matches. To see a partial match, imagine you have a list of numbers: 123, 456, 789. You search for “23*”. The pattern means “starts with 23”. The number 123 becomes the string “123”, which does not start with “23” (it contains “23” but not at the start). The number 456 does not contain “23”. The number 789 does not. So no match. If you search for “*23*”, it matches “123” because “23” appears anywhere. This illustrates that wildcards change the matching logic.

How Partial Matching Works with Numbers

When you apply a wildcard to numbers, the system treats the number as a text string. This is a crucial distinction. For example, the number 10 and the number 100 are different strings, but a partial search for “10” would match both “10” and “100” because “10” is a substring of “100”. This can lead to unexpected results if you are thinking numerically. In databases and spreadsheets, you often need to explicitly convert numbers to text before applying wildcard patterns, or use functions that handle numeric partial matching directly.

Common Wildcard Patterns and Their Meanings

• * – Matches any sequence of characters (including none). For example, “12*” matches “12”, “123”, “1200”.
• ? – Matches exactly one character. For example, “1?” matches “10”, “11”, “12”, but not “1” or “100”.
• ~* – In some systems, a tilde escapes the wildcard, so “~*” matches a literal asterisk.
• [0-9] – In regex-based systems, this matches any single digit. This is not a standard wildcard but is common in advanced matching.

Practical Applications

Partial matching with numbers and wildcards is used in many real-world scenarios:

• Spreadsheet lookups: In Excel, the VLOOKUP and MATCH functions support wildcards, but only when the lookup value is text. To use wildcards with numbers, you often need to convert the number to text first (e.g., using TEXT function).
• Database queries: SQL’s LIKE operator uses % and _ as wildcards. To match numbers, you compare against a column that is stored as a string, or you cast the number to a string.
• File searching: Operating systems allow wildcard searches for filenames, which are always strings, so numbers in filenames are matched as text.
• Log analysis: Searching for patterns like “error 5*” in logs to find all error codes starting with 5.

Common Mistakes and How to Avoid Them

One common mistake is assuming that wildcards work numerically. For example, the pattern “1*” matches 1, 10, 100, 123, but also 1.5, because the decimal point is a character. If you want to match numbers between 1 and 19, a wildcard is not the right tool; you should use a numeric range (e.g., BETWEEN 1 AND 19). Another mistake is forgetting that wildcards are case-insensitive in some systems but not others. For numbers, this is irrelevant, but for text it matters. Also, be aware that leading zeros matter: the string “01” is different from “1”, so a pattern “1*” will not match “01”.

Choosing the Right Approach

When you need partial matching with numbers, consider these options:

• Convert to text: If your data is numeric, convert it to text before applying wildcards. This gives you full control over the pattern.
• Use numeric functions: For simple cases like “starts with” or “ends with”, you can use arithmetic or string functions like LEFT, RIGHT, or MID in spreadsheets, or SUBSTRING in SQL.
• Use regular expressions: For complex patterns, regex is more powerful than wildcards, but it is also more complex. For example, regex can match a number with exactly two digits, or a number between 100 and 199.
• Consider performance: Wildcard searches on large datasets can be slow because they often prevent index usage. If performance is critical, consider storing numbers as text with an index, or using a full-text search engine.

Frequently Asked Questions

Can I use a wildcard with a number in Excel?

Yes, but only if the number is stored as text. If the cell contains a number, you can use the TEXT function to convert it, e.g., =VLOOKUP(“12*”, TEXT(A1:A10,”0″), 1, FALSE). However, this often requires array formulas or helper columns. For simple cases, consider using SUMPRODUCT with ISNUMBER(SEARCH()) instead.

Why does “10*” match “100” but not “10.5”?

Because the asterisk matches any sequence of characters, including the decimal point and digits after it. So “10*” matches any string that starts with “10”, which includes “100” and “10.5”. If you want to exclude decimals, you need a more specific pattern like “10[0-9]*” or use a numeric range.

What is the difference between * and ? in wildcards?

The asterisk (*) matches zero or more characters, while the question mark (?) matches exactly one character. For example, “1?” matches “10”, “11”, “12”, but not “1” or “100”. “1*” matches all of those plus “100” and “12345”.

How do I match a literal asterisk in a wildcard search?

In most systems, you need to escape it. In Excel, you use a tilde (~) before the asterisk, so “~*” matches a literal asterisk. In SQL, you can use an escape character with the ESCAPE clause, e.g., LIKE ‘100!%’ ESCAPE ‘!’. In regex, you escape with a backslash: \*.

Are wildcard searches case-sensitive?

It depends on the system. Excel’s wildcards are case-insensitive by default. SQL’s LIKE is case-insensitive in many databases (like MySQL) but case-sensitive in others (like PostgreSQL with default settings). For numbers, case is irrelevant, but for text it can be important.

Can I use wildcards with negative numbers?

Yes, but the minus sign is treated as a character. For example, the pattern “-1*” matches “-10”, “-123”, but not “-1” if you require at least one more character. If you want to match any negative number, you might use “-*” which matches any string starting with a minus sign.

Related

• Last Asylum Plague Beginner FAQ: Team, Upgrades, Resources
• Raster Image Processor: How It Prepares Print Jobs
• How Much RAM Do You Need? When More Helps

in t | Total time | min | 90 | 0
in d | Distance | km | 15 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2