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

how to Slice a String in Google Sheets 14

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.

See also  How to Sort Alphabetically in Google Sheets (A-Z or Z-A)

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.

See also  How To Make a Schedule on Google Sheets [2 Methods]

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.

See also  How to Change Text Case in Google Sheets [Complete Guide]

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.

See also  How to Automatically Sort Dataset in Google Sheets

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

See also  How to use Mail Merge in Google Sheets [User Guide]

Frequently Asked Questions

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

See also  How to Use HLOOKUP Function in Google Sheets (User Guide)

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. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general