Are you looking to check and extract texts from a list in Google Sheets if the first n characters are in caps or upper case letters? It may seem easy using the LEFT and EXACT function combination, but there’s a catch. I’m here to explain why using just the LEFT function with EXACT might lead to failure.
Let’s take a look at an example. Consider the two strings below and the formula to its right:
The LEFT function extracts the first n characters, and the EXACT function tests it with the capitalized first n characters. In the first case, the test would be like =EXACT("TEST","TEST"), and since it matches, the formula would return TRUE. But in the second case, the test would be like =EXACT("TE_S","TE_S"), and it would also return TRUE.
However, the formula should actually return FALSE because all the characters are not in uppercase. There is a special character between the extracted 4 characters from the left of the string.
To correctly check whether the first n characters are caps or small in a list in Google Sheets, you must know how to include special characters in the test. Let me guide you through the process.
How to Test Whether First N Characters Are Caps in Google Sheets
If you know how to check whether the first n characters are caps (or small) using a formula, filtering or extracting the same will be a breeze. So let’s dive into the steps.
1. Use RegexReplace to Replace Special Characters with the Small Letter ‘a’
Why is this required? If there are any special characters in the extracted 4 letters, the formula should treat it as a FALSE (mismatching) outright. For example, consider the string “#TESTING”. The first four characters in this case are “#TES”, and they are not caps.
To address this, we need to replace any special characters (and numbers) with a small case letter. We can use the letter ‘a’ for simplicity, but feel free to choose any other small case letters.
The formula for RegexReplace is as follows:
=REGEXREPLACE(A2, "[^a-zA-Z]+", "a")2. Use the LEFT Function to Extract N (4) Characters from the Left of a String
Now, let’s extract the first four characters from the left of the value in A2 using the formula:
=LEFT(REGEXREPLACE(A2, "[^a-zA-Z]+", "a"), 4)Remember to replace A2 with the RegexReplace formula mentioned above.
3. Test Whether First N Characters Are Caps
We can now use the EXACT function to check whether the first n characters are caps in a provided string in Google Sheets. The EXACT function takes two arguments: “string1” and “string2”.
In our case, “string1” is the formula from step 2, and “string2” is the same formula wrapped inside the UPPER function.
The formula in cell B2 would be:
=EXACT(LEFT(REGEXREPLACE(A2, "[^a-zA-Z]+", "a"), 4), UPPER(LEFT(REGEXREPLACE(A2, "[^a-zA-Z]+", "a"), 4)))To cover the entire list, just drag this formula down or make it an array formula:
=ArrayFormula(IF(A2:A="",,EXACT(LEFT(REGEXREPLACE(A2:A, "[^a-zA-Z]+", "a"), 4), UPPER(LEFT(REGEXREPLACE(A2:A, "[^a-zA-Z]+", "a"), 4)))))How to Filter If First N Characters Are Caps in Google Sheets
Now that we have covered the matching part, let’s talk about the array formula in C2. Fortunately, there are no complications here.
The formula to filter the text strings from a list if the first n characters are in uppercase would be:
=ArrayFormula(IF(B2:B=TRUE, A2:A, ))Feel free to replace B2:B with the EXACT based final formula mentioned above.
If you want, you can remove the inside ArrayFormula as the outer ArrayFormula will take care of it.
The final formula to filter text strings from a list if the first n characters are uppercase is:
=ArrayFormula(IF(IF(A2:A="",,EXACT(LEFT(REGEXREPLACE(A2:A, "[^a-zA-Z]+", "a"), 4), UPPER(LEFT(REGEXREPLACE(A2:A, "[^a-zA-Z]+", "a"), 4))))=TRUE, A2:A, ))Filter/Check Whether First N Letters are Small Letters
I’m confident that you are now able to rewrite the formulas above to test a list for the first n small letters and filter accordingly.
For your convenience, there are two changes you need to make in the formulas (in B2 as well as C2 formulas):
- Replace the function UPPER with LOWER.
- Replace the small case letter “a” in the formula to replace special characters with a capital “A”.
That’s all there is to it! Enjoy filtering and checking whether the first n characters are caps or small in Google Sheets.
If you want to learn more helpful tips and tricks, visit Crawlan.com for expert advice on all things Google Sheets.
