How to Slice a String in Google Sheets [4 Easy Methods]

how to Slice a String in Google Sheets 14
Key Takeaways: How to Slice a String in Google Sheets

To Slice a String in Google Sheets

Using the RIGHT Function:

  1. RIGHT extracts a substring from the right side of a string.
  2. The syntax is RIGHT(String, numbers-to-retain).
  3. The specified number of characters from the right is retained; the rest are removed.

Using the LEFT Function:

  1. LEFT extracts a substring from the left side of a string.
  2. The syntax is the same as RIGHT: LEFT(String, numbers-to-retain).

Using the MID Function:

  1. MID is used to extract a segment from a string.
  2. The syntax is MID(cell_reference, start_position, length).
  3. This function allows control over both the left and right sides of the string.

Using the REPLACE Function:

  1. REPLACE is a function that replaces characters in a string.
  2. The syntax is REPLACE(text, position, length, new_text).
  3. 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.

Syntax

RIGHT(String, numbers-to-retain)

Here,

  1. String: it can be a string, number, or character
  2. 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

Step 1

Sample data

how to Slice a String in Google Sheets 1

Step 2

Writing function in an adjacent cell

how to Slice a String in Google Sheets 2

Step 3

Passing cell address as the first argument

how to Slice a String in Google Sheets 3

Step 4

Passing the numbers to be retained (e.g. 2)

how to Slice a String in Google Sheets 4

Step 5

the result

how to Slice a String in Google Sheets 5

2 characters will be retained on the right side and the rest will be removed.

Note: You cannot use negative values like JavaScript.

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.

Syntax

LEFT(String, numbers-to-retain)

Same syntax as a RIGHT function

Let’s implement it quickly

Step 1

Sample data

how to Slice a String in Google Sheets 6

Step 2

Formula

how to Slice a String in Google Sheets 7

Step 3

Pass the first argument

how to Slice a String in Google Sheets 8

Step 4

Pass the second argument

how to Slice a String in Google Sheets 9

Step 5

The result

how to Slice a String in Google Sheets 10

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

Syntax

MID(cell_reference,start_position,length)

Here,

  1. cell reference: it can be a cell’s reference or a direct value (String in double quotes), and a number can be directly passed
  2. start position: it is a number (must be greater than 0)
  3. length: it is the length in number to be returned by the function

Step 1

Sample data

how to Slice a String in Google Sheets 11

Step 2

Writing the MID function in any adjacent cell

how to Slice a String in Google Sheets 12

Step 3

Pass the first argument (cell reference or value)

how to Slice a String in Google Sheets 13

Step 4

Pass the second argument (start position)

how to Slice a String in Google Sheets 14

Step 5

Pass the third argument (length to be returned)

how to Slice a String in Google Sheets 15

Step 6

The overall formula will be:

=MID(E2,6,3)

Step 7

Press Enter key and you’re done.

how to Slice a String in Google Sheets 16

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

Syntax

REPLACE(text, position, length, new_text)

here

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

Step 1

Sample data

how to Slice a String in Google Sheets 17

Step 2

Write the Function

how to Slice a String in Google Sheets 18

Step 3

Pass the first argument

how to Slice a String in Google Sheets 19

Step 4

Pass the second argument

how to Slice a String in Google Sheets 20

Step 5

Pass the third argument

how to Slice a String in Google Sheets 21

Step 6

Pass the fourth argument

how to Slice a String in Google Sheets 22

Step 7

Press Enter key, and you are done

how to Slice a String in Google Sheets 23

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

Conclusion

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq, I am a highly skilled technical writer working full-time for Office Demy. I am specialized in Google Workspace and Microsoft Office applications. With a background in Software Engineering, I possess a deep understanding of the intricate functionalities and features of these productivity tools. Connect me on Linkedin https://www.linkedin.com/in/shaiq-ansari/

OfficeDemy.com
Logo
Enable registration in settings - general