Remove First Character(s) From Left – Excel & Google Sheets
In this Article
This tutorial will demonstrate how to remove the first character(s) from the left from a string of text in Excel and Google Sheets.
REPLACE Function
To remove characters from the left of a cell, we can use the REPLACE function to replace the first characters with an empty string.
1 |
=REPLACE(B3,1,C3,"") |
MID Function
We can also remove characters from the left of a cell by using the MID Function to return all the characters to the right of a specific position in a cell, for example, to the right of a space. We would use the SEARCH Function to find the space and then the MID Function to return the characters to the right of that space.
1 |
=MID(B3, SEARCH(" ", B3)+1, 999) |
We will walkthrough this below.
SEARCH Function – Find the Space in a Cell
First, we used the SEARCH Function to find the position of the space between the first and last names.
1 |
=SEARCH(" ", B3) |
We then add one onto the value returned by this formula to get the starting position of the last name.
1 |
=SEARCH(" ",B3)+1 |
MID Function (Last Name)
Next we use the MID Function to return all the characters after the space plus 1 (the last name)
1 |
=MID(B3, C3+1, 999) |
Combining these 2 functions gives us the original formula for the last name.
1 |
=MID(B3, SEARCH(B3, " ")+1, 999) |
RIGHT FUNCTION
We can also remove characters from the left of a cell by using the RIGHT Function to return a certain number of characters from the right. We use the LEN Function to count how many characters are in the cell, allowing us to remove n (ex. 1) characters from the left:
1 |
=RIGHT(B3,LEN(B3)-C3) |
We will walkthrough this below.
LEN Function – Count Characters in a Cell
First, we will use the LEN Function to count the number of characters in the cell:
1 |
=LEN(B3) |
In the next section, we will use the RIGHT function to trim off a certain number of characters from the left.
RIGHT Function
The RIGHT Function returns a certain number of characters from the right side of a cell. For the number of characters, we will use the result of the LEN Function minus the number of characters to remove (ex. 1):
1 |
=RIGHT(B3, D3-C3) |
Combining these functions yields the original formula.
1 |
=RIGHT(B3,LEN(B3)-C3) |
Remove First Character(s) From Left in Google Sheets
All the examples explained above work the same in Google sheets as they do in Excel.