You may want to remove the first n characters in Google Sheets to clean text strings. Which is the suitable function to do this?

To remove the first n characters in a single cell or column, you can use the functions REPLACE, RIGHT+LEN, MID+LEN, REGEXREPLACE, or REGEXEXTRACT functions.

The simplest option is the REPLACE function. We will start with that and include other options as well for educational purposes.

Removing the First N Characters Using the REPLACE Function in Google Sheets

Assume cell A1 contains the value “Mr. John”. To extract the name only from this string and remove the title “Mr. ” (a total of 4 characters), use the following formula in cell B1:

=REPLACE(A1, 1, 4, "")

This formula follows the syntax REPLACE(text, position, length, new_text), where:

• text: The cell reference (A1 in this case)
• position: The starting position of the text to replace (1 here, meaning the first character)
• length: The number of characters to replace (4 here, the length of “Mr. “)
• new_text: The text to replace with (“” here, an empty string)

The REPLACE function replaces part of the text in cell A1, starting from the first character and spanning 4 characters, with an empty string, effectively removing “Mr. ” and leaving “John”.

Note: When you use the REPLACE function to replace the first n digits in a number, the output will be text formatted. In that case, use the VALUE function to convert it back to a number, as shown below:

=VALUE(REPLACE(A1, 1, 5, ""))

This formula will return 67890 if the number in cell A1 is 1234567890.

Removing the First N Characters from Texts in a Column

In the following example, names with their titles are in column A, range A2:A15. Each title is 4 characters long, such as “Mr. ” and “Ms. “.

You can use ARRAYFORMULA with REPLACE to apply the formula to all rows in the column without needing to copy and paste it individually.

Here’s how to do it:

=ArrayFormula(REPLACE(A2:A15, 1, 4, ""))

Apply this formula in cell B2 to remove the titles “Mr. ” or “Ms. ” from the names in column A.

What About Texts in Multiple Columns?

Yes! The REPLACE and ARRAYFORMULA combo will work with data in multiple columns.

Example:

=ArrayFormula(REPLACE(A2:B10, 1, 3, ""))

Alternative Solutions

As mentioned earlier, other functions can be used to remove the first n characters from a text. I am listing them below so you are aware of them when you encounter them in a shared sheet. Replace n with the number of characters you want to remove.

=RIGHT(A1, LEN(A1)-n)

Extracts all characters from the right of cell A1, excluding the first n characters.

=MID(A1, n+1, LEN(A1))

Extracts all characters from cell A1 starting at position n + 1 to the end of the string.

=REGEXREPLACE(A1, "^.{n}", "")

Removes the first n characters from the text in cell A1.

=REGEXEXTRACT(A1, REPT(".", n) & "(.*)")

Extracts all characters from cell A1, excluding the first n characters.

All these formulas will work with the ARRAYFORMULA function, similar to the REPLACE function. Please note that REGEXREPLACE and REGEXEXTRACT are specifically for text, not numbers.

Resources

• How to Group a Column Based on First Few Characters in Google Sheets
• Clean Function in Google Sheets and Non-Printable Characters
• Regex to Replace the Last Occurrence of a Character in Google Sheets
• How to Check First N Characters Are Caps or Small in a List in Google Sheets
• Insert Delimiter into a Text After N or Every N Character in Google Sheets
• Remove Repeated Characters from the End of Strings in Google Sheets
• Counting a Specific Character in Google Sheets
• Remove or Replace Last Character from a String in Google Sheets