As part of cleaning text strings, I want to remove first n characters in Google Sheets not only in a single cell but also in multiple columns. Which is the suitable function to do this?
To remove the first n characters in a single cell or as a batch in multiple cells in one single column or multiple columns you can use either of the following Google Docs Sheets functions.
1. SUBSTITUTE
2. REPLACE
3. REGEXREPLACE
4. RIGHT
5. MID
The above functions will be helpful in removing part of the text from left. Along with the function RIGHT you may want to use the function LEN too.
Remove the First N Characters in a Cell in Google Sheets
Assume cell A1 contains the value “Mr. John”. I want the formula to extract the name only from this string leaving the title.
To remove first n characters in a cell here first I am using the Right and Len combination formula. Other formulas follow.
Formula 1 (Works in Excel and Google Sheets)
In this formula, I am removing the first 4 characters from the text string. You can replace that number as per your text cleaning requirement.
=right(A1,len(A1)-4)
The above is the most commonly used formula in Google Sheets as well as in Excel to remove certain numbers of characters from a text string. Of course, the cell reference may differ.
Formula 2 (Works in Excel and Google Sheets)
Here directly replacing the unwanted text string.
=SUBSTITUTE(A1,"Mr. ","")
Formula 3 (Works in Excel and Google Sheets)
This formula replaces the first 4 characters.
=replace(A1,1,4,"")
In this formula also you can specify the number of characters to be removed from the left of a string.
Formula 4 (Works in Google Sheets Only)
You can use RE2 regular expressions to remove the first n characters in Google Sheets.
=REGEXREPLACE(A1,"Mr. ","")
Formula 5 (Works in Excel and Google Sheets)
This formula may new to you but not the function. Yes, the function MID is also useful to remove the first n characters in Google Docs Sheets and Excel.
=mid(A1,5,9^9)
Here take care of one thing. The number of characters to remove from the text is 4, but you should put 5 in the formula. The 9^9 just represent a big number. Use it as it is.
Remove the First N Characters in a Single Column in Google Sheets
You can convert few of the above formulas to Array Formulas to remove n number of characters in a column.
Apply the below formula in cell B1 to remove the title Mr. or Ms. from the names in column A.
=ArrayFormula(right(A1:A10,len(A1:A10)-4))
You can also use any of the below two formulas.
Array Formula 1:
=ArrayFormula(replace(A1:A10,1,4,""))
Array Formula 2:
=ArrayFormula(mid(A1:A10,5,9^9))
Remove the First N Characters in Multiple Columns in Google Sheets
Similar to the above example, you can use MID, RIGHT or REPLACE formula to remove first n characters in multiple columns in Google Sheets.
See how to remove first three characters in a two column data range using the REPLACE function.
=ArrayFormula(replace(A1:B10,1,3,""))
See the screenshot. You can use the above other two formulas similarly.
That’s all. Enjoy!
Additional Resources:
1. How to Highlight Only Texts in a Column or Row in Google Sheets.
2. Filter Rows If All the Columns Have Text Content in Google Sheets.
3. Extract All Numbers from Text and SUM It in Google Sheets.
4. How to Wrap Text Using Formula in Google Sheets [One Word Per Line].
5. Difference Between JOIN, TEXTJOIN, CONCATENATE Functions in Google Sheets.
6. Google Doc Spreadsheet – Change Text to Upper, Lower and Sentence Case.
7. The Formula to Reverse Text and Numbers in Google Sheets.
