To Slice a String in Google Sheets
Using the RIGHT Function:
- RIGHT extracts a substring from the right side of a string.
- The syntax is RIGHT(String, numbers-to-retain).
- The specified number of characters from the right is retained; the rest are removed.
Using the LEFT Function:
- LEFT extracts a substring from the left side of a string.
- The syntax is the same as RIGHT: LEFT(String, numbers-to-retain).
Using the MID Function:
- MID is used to extract a segment from a string.
- The syntax is MID(cell_reference, start_position, length).
- This function allows control over both the left and right sides of the string.
Using the REPLACE Function:
- REPLACE is a function that replaces characters in a string.
- The syntax is REPLACE(text, position, length, new_text).
- By using an empty string as “new_text,” you can effectively remove characters from the string.
Hi. In this article, we will learn how to slice a string in Google Sheets. Slicing a string means breaking a string and extracting a substring from it such as a string “String”, I want to slice it and want to extract only the “ring”. So, this is what we say slicing a string.
Now you can say it can be done manually, why do we need to learn it? Ok so if you have a database that has huge data and you want to get the substrings, can u do it manually now? Of course not. So, in this article, we will learn how to slice a string in Google Sheets using various functions in google sheets. the term slice is normally used in programming to get a substring, you can also say this as removing a character from a string or more easily you can say taking a substring from a string. Now also note that String does not only mean a word, a name, or a character. The methods we will learn apply to numbers as well.
Use case of Slicing a string in Google Sheets
We have to deal with large data sets in google sheets, sometimes we may encounter scenarios where we need to extract the substring from an already written string. So, in such situations, we need to have some methods to slice a string from the start, mid, or end. It’s highly used in automation when you use add-ons like mail merge. Or you use any mailing add-on which automatically takes user detail from the sheet.
Slice can be a useful feature there, we can collect the first name from the full name, we can also collect the last name from it, or we can also collect the first character or neglect the first character. We can do anything on any side of a string, and that’s what we are going to learn in this article. So, I hope you have got the basic idea of what is String slicing, and why we need to learn how to slice a string in google sheets.
How to Slice a String in Google Sheets
Here, we will see the step-by-step procedure required to perform a string slice, and get a substring. We will first see sample data from where I need to collect a substring. I will use various functions to perform the same task, you can pick the function that suits you, and your problem better.
Slice a String in Google Sheets – Using the RIGHT Function
The RIGHT function is a string extraction function used in google sheets to get s substring from a String on the right side. In other words. It gives you a substring from the end of a specified String, which means it removed the substring from the left-hand side.
- String: it can be a string, number, or character
- numbers to retain: They can be numbers like 1,2,3 to retain on the right side (N-1) will be removed.
Let’s implement this function to extract a substring
Writing function in an adjacent cell
Passing cell address as the first argument
Passing the numbers to be retained (e.g. 2)
2 characters will be retained on the right side and the rest will be removed.
Slice a String in Google Sheets – Using the LEFT Function
In this section, we will learn how to slice a String in google sheets using the LEFT function, now this function also works very similarly to the RIHT function. The difference is only in the position, the right function returns the values of the right side and removes them from the left, here LEFT function returns the value from the left side and removes them from the right side.
Same syntax as a RIGHT function
Let’s implement it quickly
Pass the first argument
Pass the second argument
This is how you can slice a string from the right side in google sheets.
Let’s say how to find a substring from the middle.
Slice a String in Google Sheets – Using the MID Function
MID is another good function to slice a string in google sheets. We can use this function to easily remove characters from a String from the left side and can also define the reminder. This means logically we can remove values from both ends. The MID function returns a segment of a String. Let’s see the syntax
- cell reference: it can be a cell’s reference or a direct value (String in double quotes), and a number can be directly passed
- start position: it is a number (must be greater than 0)
- length: it is the length in number to be returned by the function
Writing the MID function in any adjacent cell
Pass the first argument (cell reference or value)
Pass the second argument (start position)
Pass the third argument (length to be returned)
The overall formula will be:
Press Enter key and you’re done.
This is how using the MID function you can get a segment of a String and can be controlled from both the left and right side.
Slice a String in Google Sheets – Using the REPLACE Function
In this section, we will learn how to slice a string in google sheets using REPLACE function, now replace function is from another family, and it has a little different logic to extract a substring from a string, it does not remove characters but it replaces its name. So let’s see the syntax and a quick example to understand this function
REPLACE(text, position, length, new_text)
text: it is any text or cell reference
position: starting position from where the replacement should take place
length: how many characters are to be replaced
new text: replace with?
So here we use the last argument as ” ” empty string to logically remove them while replacing
Let’s see an example
Write the Function
Pass the first argument
Pass the second argument
Pass the third argument
Pass the fourth argument
Press Enter key, and you are done
This is how to slice a String in google sheets using replace function.
Download/Copy Google Sheets Practice Workbook
Useful Notes on Slice a String in Google Sheets
- You can pass direct values in any of the above functions to get their substrings. Although passing a direct String to get a substring does not make any sense but you may encounter a rare situation where you need this functionality.
- You can pass only 2 arguments in LEFT and RIGHT functions
- You can pass 3 arguments in the MID function
- MID function’s second parameter is start position, if I pass here 2 then it will start from the second character of my string but will not remove it it will remove what is before the 2 means if I pass 2, only 1 character will be removed, if I pass 1 nothing will be removed.
- The third parameter of the MID function is a length to return, if you have a small String you can write here 10 20, or anything which you think is bigger than your String length, or you can use here a LEN function to return LEN-Start-position
Frequently Asked Questions
Can I use REGEX Functions to Slice a String in Google Sheets?
Yes, REGEX functions can be used for string manipulation for numeric extraction in Google Sheets. These functions allow you to extract specific numeric values from a string by defining patterns with regular expressions. Utilizing REGEX functions in Google Sheets provides a powerful way to slice and extract desired information from strings.
What does the LEFT function do in google sheets?
The LEFT function returns a substring from the left of a string specified in the function. We have to pass two mandatory arguments, the first argument is the cell reference, and the second argument is the length to be returned from the right side of the string
What does the RIGHT function do in google sheets?
The RIGHT function returns a substring from the right of a string specified in the function. We have to pass two mandatory arguments, the first argument is the cell reference, and the second argument is the length to be returned from the right side of the string
What does the MID function do in google sheets?
The MID function returns a segment of a string we specified in the function. We have to pass three mandatory arguments, the first argument is the cell reference, the second argument is the start position from where the function will start to remove the character on the left side, and the third argument is the length which will the total length of the String after returning. This way we can skip both LEFT and RIGHT functions to use the MID function and take control on both sides of our string to extract a substring
Wrapping up how to slice a string in google sheets. We discussed what is meant by slicing a string in google sheets. We then saw why we need to slice a string in google sheets. Then we saw three functions to perform string slicing practically using each function with examples. We then moved further and we checked out another function, which is a REPLACE function, we saw how logically we can remove a part of a string and extract a substring from it using REPLACE function. So, in total, we saw four powerful functions to solve this problem. We have now learned how to slice a string in google sheets using various methods.
So that’s all from how to slice a string in google sheets. I will see you soon with another helpful tutorial till then take care. Thank you so much for reading. Keep learning with Office Demy.