How to Remove the First Character in Google Sheets (4 Formulas)

When we enter data in Google Sheets or copy and paste data from somewhere, it’s common to have some mistakes or typos in the first character of text data, so we need to remove them and clean the data. To remove the first character from a dataset in Google Sheets, we can use and combine a bunch of functions, including REPLACE, RIGHT with LEN, REGEXREPLACE, and MID.

We will explain the uses and the right formula for all of these functions to help you remove any unnecessary first character from the dataset in Google Sheets.

The REPLACE function is the easiest way to remove the first character from a Google Sheets dataset in bulk.
The REPLACE function replaces the selected character with an empty string, so that character will be deleted or removed, and the other characters will be returned. The general formula for removing a character with this function is: REPLACE(original_text, start_num, num_chars, new_text). 

Here,
Original_text = Cell reference or any text data in your dataset.
Start_num = Usually the first number or 1.
Num_chars = The position number of the character you want to remove. Like, if you want to remove the first character, you need to replace “num_chars” with 1.
New_text = Write the new text you want to see as a replaced one. Such as, if you simply remove the first character, keep this “ new_text” empty, like “”.

We will use a sample employee dataset below to explain how you can use the REPLACE function to remove the first character in Google Sheets.

Here, we can see, all the names in Column A have the character “@” in front. We will remove them.

Steps:

➤ First, select cell F1 and give it a name.
➤ Now, insert the following formula in cell F2.

➤ Then, press ENTER and the name in cell A2 will be returned without the first character, i.e., “@”

➤ Finally, select the bottom left corner of cell F2 and drag the cursor down to fill all the data.

The LEN function counts all the characters, and using a combined formula with the RIGHT function, we can remove the first character from a dataset in Google Sheets.

The RIGHT function returns the rightmost characters of a string with the specified number of characters you want, while the LEN function simply returns the number of characters in a string. Combining these two functions, you can show the desired characters of any string data.

Steps:

➤ First, click on cell F1 and write a preferred name.
➤ Then, in cell F2, insert the following formula:

➤ Now, press ENTER, and you will see the first name without the first character.

➤ Finally, select the small dot at the bottom right corner of cell F2 and drag the cursor down to get all the names without the first character.

If in your dataset, a few entries have typos or symbols as the first character that you need to remove, then the REGEXREPLACE function is the right choice. Because this function does not remove any letters, instead it only removes the symbols.

We will use the below dataset to explain the use of REGEXREPLACE to remove the first character (symbol) from a dataset in Google Sheets.

Here, we can see that in column A, a few names have “@” at the front and some do not.,

Steps:

➤ First, select cell F1 and give it a name.
➤ Then, click on cell F2 and insert the following formula:

➤ Press ENTER on your keyboard, and the first name will be returned without the first symbol.

➤ Now, drag the cursor till all the names have been formatted.

Here you can see, the names without “@” as the first character remained the same, only the symbols are removed.

The MID function is useful to remove the first character in Google Sheets. However, it removes every type of first character regardless of letter or symbol. Below, we have explained how you can use this function.

Steps:

➤ First, select cell F1 and use a relevant name for it.
➤ Then, select cell F2 and insert the following formula:

➤ Finally, press ENTER and drag the cursor to fill in all the data.

Frequently Asked Questions

How Can I Remove the First “n” Characters from String Data in Google Sheets?

You can easily remove the first “n” characters from string-type data in Google Sheets. To do it, we will use the  REPLACE function. Suppose your data is in cell A2. Then, select an empty cell and write down this formula: =REPLACE(A1, 1, N, “”). It will remove the first “n” characters from the data in cell A2.

How Can I Remove Only a Specific First Character from Google Sheets?

To remove a specific first character from Google Sheets, we can use the REPLACE function. The formula to do this is: =REPLACE(A1, 1, n, “”). Here, “n” is the number of the first characters you want to remove. Such as if your data is “123Anna” and you want to remove “123” from it, then modify the formula as =REPLACE(A1, 1, 3, “”).

How to Remove the First Character from Multiple Cells in Google Sheets?

To remove the first character from multiple cells at once in Google Sheets, we will use ARRAYFORMULA. Suppose your dataset is in A2 to A15 cells. Then, in an empty cell, write down this formula: =ARRAYFORMULA(RIGHT(A1:A15, LEN(A1:A15) – 1)). Remember to change the cell reference, “A1:A15”, according to your data.

Wrapping Up

In this article, we have learned to use REPLACE, RIGHT and LEN, REGEXREPLACE, and MID formulas to remove the first character from our Google Sheets dataset. Try these formulas out and let us know your experience.