Mehrab Imtiaz
Wildcards can be a staple when it comes to customizing searches in any Google workplace application. So today, we will answer a few questions and discuss a few points focusing on whether we can use Find and Replace with a wildcard in Google Sheets.
How to Find and Replace with Wildcard in Google Sheets
What are Wildcards?
Wildcards are special characters or symbols used to fine-tune your searches in Google Sheets. Within a function, these symbols represent characters or strings. In Google Sheets, we have three different wildcard characters:
- Asterisk (*): This wildcard can represent any number of characters. For example, ‘Bill*’ can mean Bill, Bills, Billy, Billiards, etc.
- Question Mark (?): This wildcard represents only a single character. For example, ‘B?ll’ can mean Ball, Bell, Bill, etc.
- Tilde (~): This wildcard symbol is usually used before either the * or ? wildcards. It tells Google Sheets that it should consider the next character as a regular character symbol and not a wildcard. For example, ‘b~*’ means to search for all strings that contain the exact text b*.
Can you use Wildcards in Google Sheets?
Yes, you can. Wildcard symbols in Google Sheets are used in certain situations:
- They’re commonly used in conditional functions. For example, COUNTIF, COUNTIFS, SUMIF, SUMIFS, etc.
- Wildcards are used to perform partial string lookups in VLOOKUP.
- They’re used to conditionally filter data.
Using asterisk wildcard to count all instances of IT in the ID column regardless of the ID number.
Can you use Wildcards in Find and Replace?
No, you cannot. Unfortunately, fine-tuning searches using wildcards are not supported in the Find and Replace dialogue box in Google Sheets. Find and Replace will simply ignore the fact that a wildcard is implemented and see it as a regular character to search. The dialogue box also does not have any options for wildcards to check.
However, Find and Replace does support Regular Expressions to fine-tune searches here. It is also safe to say that we can customize our searches beyond the limitations of wildcards using regular expressions.
1. Fine Tune Find and Replace with Regular Expressions in Google Sheets
What are Regular Expressions?
Regular expressions are a sequence of symbols or characters that we used to represent other characters and strings. Regular expression symbols are also known as ‘metacharacters’. The following is a list of these metacharacters and what they mean:
| Character | Meaning |
|---|---|
| ^ | The beginning of the string |
| $ | The end of the string |
| . | A single character |
| ? | Zero or one occurrence of a character or string |
| * | Zero or more occurrences of a character or string |
| + | One or more occurrences of a character or string |
| | | The Or operator |
| () | This holds a group of metacharacters inside it and represents that sequence of characters |
| [] | This holds a set of characters and represents any one of the characters inside it |
| [^] | This holds a set of characters and represents any one of the characters not listed inside it |
| \ | This is used to escape a special character |
Now that we understand what regular expressions are and what they can do, let’s use them in our dataset to find all the instances that start with the letters ‘IT’ in the ID column. Much like what we did with COUNTIF using wildcards in the previous section.
Using Regular Expressions to Find and Replace Partial String Matches
In the Find and Replace dialog box (Keyboard shortcut: CTRL+H, or find it from the Edit tab), set up the following search conditions:
- Search for ‘^IT’. This regular expression searches for all the cells that start with the letters “IT”.
- Search over a Specific range. We have included only the ID column, C3:C12.
- Check the Search using regular expression option. You must enable this option to get the correct result.
Click Find to cycle through all the instances that match:
As you can see, Find and Replace has found all four instances where the values start with the letters IT using regular expression. This is correct as we have seen the same count number previously with COUNTIF and wildcards.
The Find and Replace option is only half as useful if we are only searching. So let’s replace all the values containing IT we have found with the word “ENG” to update the ID.
For a more in-depth breakdown of the Find and Replace function covering more scenarios, please see our Find and Replace in Google Sheets article.
Read More: How to Use Find and Replace in Column in Google Sheets
Similar Readings
- How to Use Wildcard with IF Condition in Google Sheets (3 Ways)
- Replace Space with Dash in Google Sheets (2 Ways)
- How to Use QUERY Function with Wildcard in Google Sheets
- Use SUMIF Function with Wildcard in Google Sheets
2. Using REGEXREPLACE Formula to Find and Replace Values in Google Sheets
Another way to find and replace in Google Sheets using regular expressions is by using the REGEXREPLACE formula. As the name suggests, the function searches for values using regular expressions and replaces them with something user-defined.
The REGEXREPLACE syntax:
REGEXREPLACE(text, regular_expression, replacement)
Where the Find and Replace option replaces the searched value within its existing cell, the REGEXREPLACE function does the same in another cell. Users can take advantage of this to keep the original data unchanged and secure.
Let’s use REGEXREPLACE to replace all the IDs that start with “IT” with “ENG”.
Our formula:
=REGEXREPLACE(C3,"^IT","ENG")
Read More: Use REGEXREPLACE to Replace Multiple Values in Google Sheets (An Easy Guide)
Final Words
While you can’t use wildcards in Find and Replace, you can use regular expressions in it to achieve virtually the same results. Where wildcards are limited to three symbols, regular expressions can take a step further to further fine-tune your searches with a combination of characters called metacharacters. That’s not to say how it can be fully taken advantage of in REGEX functions like REGEXREPLACE.
Please feel free to leave any queries or advice you might have for us in the comments section below.
Related Articles
- How to Search in Google Spreadsheet (5 Easy Ways)
- Search in Google Spreadsheet (5 Easy Ways)
- How to Use Wildcard in Google Sheets (3 Practical Examples)
- [Fixed!] Wildcard Is Not Working in Google Sheets (5 Solutions)
- How to Find Trash in Google Sheets (with Quick Steps)
- Find Value in a Range in Google Sheets (3 Easy Ways)
- How to Use Filter with Wildcard in Google Sheets (3 Examples)
- Find All Cells With Value in Google Sheets (An Easy Guide)
Hi.
So how do I, for example, make ABC-123 into ABC. In excel you just replace “-*” with nothing and voila you get ABC. Can you do the same in Google Sheets and if you do – how?
Thanks
Hi Max,
I am assuming that you are thinking about extracting the letters ABC from ABC-123. In this case, you can approach it in two ways without even using wildcards.
The first way is to use text functions to extract the number of characters from the string:
=LEFT(B2,LEN(B2)-4)
The LEN(B2)-4 removes the last 4 characters from the string.
For a more dynamic approach, you can look for the hyphen/dash as a delimiter to remove characters before or after it. In this case, to get ABC from ABC-123, the formula will be:
=MID(B2,1,FIND(“-“,B2)-1)
Just replace “-” with any delimiter of your choosing.
However, if you still want to extract using regular expression to get ABC from ABC-123, use this:
=REGEXREPLACE(B2,”[0-9-]”,””)
The regular expression to remove all digits and hyphens is given by [0-9-].
I hope this helps!