How to Separate Text and Letters in Google Sheets using REGEXREPLACE

To remove letters from a string and keep only the non-letter characters, such as numbers, you can use the REGEXREPLACE formula in Google Sheets. For example, given the input string “2c11ou0n2tL0e0n6” in cell B3, if you want to keep only the letters and remove the non-letter characters, use the formula:

=REGEXREPLACE(B3, “[^a-zA-Z]”, “”)

Problem String “2c11ou0n2tL0e0n6”

The result will be: countLen

Alternatively, if you want to keep only the non-letter characters and remove the letters from the same string “2c11ou0n2tL0e0n6” use the formula:

=REGEXREPLACE(B3, “[a-zA-Z]”, “”)

The result will be: 21102006

Explanation: REGEXREPLACE

Formula: =REGEXREPLACE(B3, “[^a-zA-Z]”, “”)

Original problems or string: “2c11ou0n2tL0e0n6”

Regex Pattern: [^a-zA-Z] matches all non-alphabetic characters,
which are the digits in the problems or string: 2, 1, 1, 0, 0, 2, 0, 0, 6

Replacement: The formula replaces matched character is replaced with an empty string (“”), effectively removing them.

Final Result: Only alphabetic characters remain, so the output is countLen
Thus, the formula cleans the string by removing all numbers and any other non-letter characters.

To remove text from letters in Google Sheets using the REGEXREPLACE function, you need to clarify whether you’re aiming to remove letters from text (keeping only non-letter characters) or something more specific. To remove all alphabetic characters (letters) from a text string and keep only numbers, symbols, or other characters, use the following formula:

Formula: =REGEXREPLACE(B3, “[a-zA-Z]”, “”)

Original problems or string: “2c11ou0n2tL0e0n6”

Pattern: [^a-zA-Z] matches all alphabetic characters, both lowercase (a-z) and uppercase (A-Z)

Replacement: The formula replaces matched letters with an empty string (“”), effectively removing them.

This keeps all non-letter characters (numbers in this case) and removes the letters, so the output is 21102006

REGEXREPLACE and Other Formulas:

=REGEXREPLACE(B3, “[^a-zA-Z]”, “”)
=REGEXREPLACE(B3, “[a-zA-Z]”, “”)
=TEXTJOIN(“”,1, SPLIT(lower(B3),“qwertyuiopasdfghjklzxcvbnm`-=[]\;’,./!@#$%^&*()”))
=JOIN(“”, ARRAYFORMULA(FILTER(MID(B3, SEQUENCE(LEN(B3)), 1), ISNUMBER(VALUE(MID(B3, SEQUENCE(LEN(B3)), 1))))))

These formulas help to filter the desired characters from the string effectively.

Income Tax Calculation in Google Sheets for Salary Employees for FY 2024-25 (AY 2025-26)

The Role of caret ( ^ ) in the REGEXREPLACE Formula

In the formula =REGEXREPLACE(B3, “[^a-zA-Z]”, “”), the caret ( ^ ) plays a crucial role within the regular expression pattern. In regular expressions, the caret (^) has different meanings depending on its position. When its placed outside of square brackets (e.g., ^abc), it signifies the start of a string and when place inside square brackets at the beginning of the pattern (e.g., [^abc]), it acts as a negation operator.

In the other pattern [ a-zA-Z ], without the caret ( ^ ), the pattern matches any single lowercase or uppercase letter and with the caret [ ^a-zA-Z ], the caret inside the square brackets negates the character set, which tells the regex engine to match any character except the letters a to z (lowercase) and A to Z (uppercase).

In the formula =REGEXREPLACE(B3, “[^a-zA-Z]”, “”), caret play a vital role to solve the problem string “2c11ou0n2tL0e0n6“. In the string the pattern [^a-zA-Z] matches 2, 1, 1, 0, 0, 2, 0, 0, 6 and these characters are removed, and display the result as : “countLen”. Thus, the caret [ ^ ] inside the brackets tells the formula to focus on removing everything that isn’t a letter.