From a list in Google Sheets, I want to check and extract texts if the first n (here 4) characters are in caps or upper case letters. This may seem easy to some of you using the LEFT and EXACT function combination, but not so.

I will explain to you why using the LEFT function with EXACT maybe a failure. As an example, consider the two strings below and the formula on to its right.

The LEFT function extracts the first n (here 4) characters and the EXACT tests it with the capitalized first n (4) characters.

In the first case, i.e., with the value in cell A1, the test would be like =exact("TEST","TEST") and since it matches, the formula would return TRUE.

In the second test with the value in cell A2, the test would be like =exact("TE_S","TE_S"). Here also it matches and returns TRUE.

Here actually the formula should return FALSE as all the characters are not in the upper case. 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.

I mean if the extracted first n characters contain any special characters or numbers (alphanumeric) consider straightaway the first n characters are not caps.

See below the sample values to test in column A, the test output in column B, and the filtering output based on first n caps in column C.

I’m going to provide you the formulas I have used in cell B2 and C2 in the below example and its explanations follow.

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, the filtering/extracting of the same will be easy for you. So I’ll come to the filtering part later.

Actually the formula in cell B2 tests the whole column A (A2:A). It tests whether the first n characters in the list in A2:A are caps. It’s an array formula.

First I’ll consider only the text in cell A2 in my test. Then I’ll take that to the whole column A2:A. Get the formula step by step below.

1. RegexReplace to Replace Special Characters with the Small Letter ‘a’

Why is this required?

I have already mentioned if a special character(s) is there in the extracted 4 letters, the formula should treat it as FALSE (mismatching) outright.

Again, for example, the first four characters in the string #TESTING are #TES. In this case, the first 4 characters are not caps.

So the idea here is to replace the special character(s), if any, and also number(s) with a small case letter. I am preferring the letter ‘a’ though you can use any small case letters.

RegexReplace Formula:

=REGEXREPLACE(A2, "[^a-zA-Z]+", "a")

2. LEFT Function to Extract N (4) Characters from the Left of a String

Formula to extract four strings from the left of value in A2 (in place of cell A2 we must use the RegexReplace formula above):

=left(REGEXREPLACE(A2, "[^a-zA-Z]+", "a"),4)

3. Formula to Test Whether First N Characters Are Caps

Now we can use the EXACT function to match/check whether the first n characters are caps in a provided string (text) in Google Sheets.

There are two arguments in the EXACT function – “string1” and “string2”.

EXACT(string1, string2)

In this, ‘string1’ is the just above formula (step 2 formula) and ‘string2’ is the same formula but wrapped inside the UPPER function.

The Formula in Cell B2:

=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 as an array formula as below.

Final 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

I am going to talk about the array formula in C2 (scroll up and see the image). Here there is no complication as we have already covered the matching part.

The formula to filter would be like this.

=ArrayFormula(if(B2:B=TRUE,A2:A,))

You May Like: IF Logical Function – Advanced Tips.

Replace B2:B with the above EXACT based final formula, which we have used in cell B2 to match/check the first n caps letters in A2:A.

=ArrayFormula(if(ArrayFormula(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,))

If you want you can remove the inside ArrayFormula as the outer ArrayFormula will take care of the inner one.

The final formula to filter text strings from a list if the first n characters are upper.

=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 know, without my help, you are now able to rewrite the formulas above to test a list for first n small letters as well as filter accordingly.

For your info, there will be two changes in the formulas (in B2 as well as C2 formulas). They are;

1. Replace the function UPPER with LOWER.
2. I have used the small case letter “a” in the formula to replace special characters. Make it capital “A”.

That’s all. Enjoy!