Ishrak Khan
As humans, it’s in our nature to either add or subtract something from our daily lives. The same is true when using Google Sheets. There are a few simple formulas in Google Sheets In order to remove the first character (or more).
A Sample of Practice Spreadsheet
4 Methods to Remove First Character in Google Sheets
We will describe all the methods available to remove the initial character in Google Sheets with the help of a clear example.
Consider a school for civil engineers where some fresh students have just enrolled for the upcoming semester. You receive a dataset from the registrar’s office to create their student ID.
1. Using the RIGHT Function with LEN
The best method for removing the first character from a cell or text string is to combine RIGHT and LEN functions.
Steps:
- Bring up the RIGHT function in cell D6 for beginning the count with the right side
- Choose the string value in cell B6.
- The entire amount of characters for cell B6 is counted using the LEN Function.
- The symbol “-1” signifies that one character will be removed.
Therefore, RIGHT(B6,LEN(B6)-1) denotes that the first character of the string value in cell B6 will be removed.
=RIGHT(B6,LEN(B6)-1)
As a result, the Student ID for Harding is 201 which does not include the letter U.
The formula below can be used if more than one character needs to be removed to make a new student ID.
=RIGHT(B6,LEN(B6)-3)
It will eliminate the first three characters from the string’s length. Therefore, the Student ID for Harding is 1.
Extra: Method to Remove the Last Character (LEFT + LEN)
Similar in syntax to RIGHT, LEFT begins operating from the final character. Therefore, we can utilize the LEFT function along with the LEN Function if we need to eliminate the last character. Similar steps as the RIGHT function.
=LEFT(B6,LEN(B6)-1)
In the image above, Harding’s Student ID is U12 excluding the last character 1.
Read More: How to Remove Last Character from String in Google Sheets
2. Application of MID Function with LEN Function
The second approach uses the MID function in addition to the LEN function in Google Sheets. With a bit of variation in syntax, it employs the same logic as the RIGHT function.
Steps:
- In cell D6, activate the MID function.
- Cell B6 is the cell reference of the string.
- Excluding the first character, the index value will be 2 to start extracting from the second character.
- The LEN function calculates that the length to be extracted is equal B6.
=MID(B6,2,LEN(B6))
We just need to alter the “Starting at” index value to remove multiple characters. For instance:
=MID(B6,4,LEN(B6))
In cell D6, the Student ID is found by removing the first three characters and starting with character number 4. Thus, the new Student ID number for Harding is 1.
Read More: How to Remove Everything after Character in Google Sheets
3. Using REPLACE Function to Remove First Character in Google Sheets
In Google Sheets, the same thing may be done in various ways using various formulas. Another easy method is to remove the last character by using the REPLACE function.
Steps:
- Add the REPLACE function to cell D6.
- Select B6 as the text replacement cell reference.
- The position will be 1 as we are removing the initial character.
- Since only one character will be changed, the length will also be 1.
- Lastly, swap it out for a blank character (“”).
=REPLACE(B5,1,1,””)
The REPLACE function can be used in this example to offer a dynamic approach. By simply entering “CE-“ as new text and setting the character length to 2, we can add the department name to the Student ID while removing the first two characters.
=REPLACE(B6,1,2,”CE-”)
So, The Student ID for Harding is CE-01.
Read More: Remove Everything after Character Using Google Sheets Formula
4. Implementation of REGEXREPLACE to Remove First Character in Google Sheets
Last but not least approach is to use the REGEXREPLACE function. REGEXREPLACE is useful for removing everything that comes before and after particular characters. The format of the formula varies depending on the text or number.
I. In the Case of the Text Value
The following are the steps for this specific example where we are removing the first text character:
- In Cell D6, include the REGEXREPLACE function.
- Choose $B6 as the cell reference for the changing text.
- Type “[U]+” to define the matching regular expression.
- Finally, substitute a blank character for it (“”).
=REGEXPLACE($B6,”[U]+”,””)
Thus, by deleting the first character of the string, we were able to obtain the Student ID of Harding.
This function also has another implication. Suppose, for instance, that you wish to add the Department Name to the Student ID but the Admission number differs and consists of three letters and three numbers. The Department Name can be used in place of the letters, and the digits will remain as a unique identification number.
Steps:
- In Cell D6, include the REGEXREPLACE function.
- Choose $B6 as the cell reference for the changing text.
- Type “[A-Za-z]+” to define all the matching letters as a regular expression.
- “CE-“ to replace and add the department name.
=REGEXPLACE($B6,”[A-Za-z]+”,”CE-”)
In light of this, Harding’s new Student ID is CE-201.
II. REGEXREPLACE in Terms of Number
You should note that this function only accepts text as input and only outputs text. It does not accept numbers. However, it is possible to change any number in a text value.
Steps:
- In Cell D6, include the REGEXREPLACE function.
- Choose B6 as the cell reference for the changing text.
- Type “[8]+” to define the matching regular numerical expression.
- Finally, substitute a blank character for it (“”).
=REGEXPLACE($B6,”[8]+”,””)
We can use this function to include the department name as a further, more general example.
Steps:
- Include the REGEXREPLACE function in cell D6.
- Select $B6 as the text replacement cell reference.
- To define the matching regular expression, type “[8,X]+” (both number and text)
- Finally, Replace the text with “CE-“
=REGEXPLACE($B6,”[8,X]+”,”CE-”)
For Harding, the new Student ID is CE-02, as seen in the process and the above photos.
Conclusion
Regarding deleting the first character, Google Sheets includes a lot of useful features. However, the amount and structure of the data affect its use and efficiency. You can substitute any string value using any of the aforementioned techniques. Check out the practice spreadsheet to acquire a thorough understanding of the features. For additional information on Google Sheets-related topics, you can also visit our OfficeWheel blog.
