How to Remove Everything after Character in Google Sheets

Suppose you have obtained a raw dataset that needs to be optimized. You may need to remove everything after a certain character to do that. This may become quite difficult to do it manually with a large dataset. So, here we will highlight how to remove everything after character in Google Sheets. Follow the article to learn to use some Google Sheets features and formulas.


A Sample of Practice ­­­­­­­­Spreadsheet


What Are Characters in Google Sheets?

Characters represent Numbers, Text, and Symbols in Google Sheets. For example, +, =, 1, A, etc. can be considered individual characters. We use characters in Google Sheets to prepare datasets, create formulas, and for many other purposes.


4 Ways to Remove Everything after Character in Google Sheets

The dataset below contains some user Profile links. Assume you need to remove everything after the ‘/’ character to extract the corresponding website links.

 google sheets remove everything after character

Here, we will highlight 4 methods to remove everything after a character in Google Sheets to obtain the desired results. So let’s start!


1. Utilizing Find and Replace Feature

You can utilize the Find and Replace feature in Google Sheets to remove everything after the ‘/’ character. Follow the steps below to be able to do that.

📌 Steps:

  • First copy range B5:B10 to range C5:C10 to avoid losing the original dataset. Then select the C5:C10 range.

Utilizing Find and Replace Feature

  • Then press Ctrl + H and the Find and replace feature will pop up.
  • Now enter /.* in the Find box. Then enter a / in the Replace with box.
  • Then check the Match case and Search using regular expressions check boxes.
  • After that press Replace all and then click on Done.

  • After we finish the method, the replaced values will be shown in the C5:C10 range.


2. Using REGEXREPLACE Function

You can also use the REGEXREPLACE function to get the same result. You can use this function to replace part of a text with another text or remove everything before or after certain characters within that text.

  • First, insert the following formula into cell C5 and then drag the Fill Handle icon below to do that.
=REGEXREPLACE(B5,"/.*","/")

Using REGEXREPLACE Function

  • Here, “/.*” means you need to replace everything after the / character. You can use “*./” instead to replace everything before the / character.

Read More: How to Remove Text after Character in Google Sheets (5 Methods)


3. Applying LEFT and FIND Functions

Alternatively, you can use the LEFT and FIND functions to do that too. Here, the FIND function will return the position of the / character within the text. Then the LEFT function will return the characters from the left of the text up to that position. Follow the steps below to see how to do it.

📌 Steps:

  • First, select cell C5 to insert the following formula into that cell.
  • Then apply the formula and drag the fill handle icon below. After that, you will see the following result.
=LEFT(B5,FIND("/",B5))

Applying LEFT and FIND Functions

  • Here, FIND(“/”,B5) return 13 as the position of the / character in facebook.com/adam is 13. Then the formula becomes LEFT(B5,13) and returns the 1st 13 characters from the text.

Read More: How to Remove Last Character from String in Google Sheets


4. Using LEFT and SEARCH Functions

The FIND function used in the earlier method is case-sensitive. But the SEARCH function is case-insensitive. So, here we will apply the LEFT and SEARCH functions to get the same results. Follow the steps below to learn how to do it.

📌 Steps:

  • First, select cell C5 to insert the formula like before.
  • Then select cell B5 followed by a comma, and insert the rest of the formula as shown below.
=LEFT(B5,SEARCH("/",B5))

Using LEFT and SEARCH Functions to remove everything after character


Things to Remember

  • Always remember, if you want to delete everything after a particular character then uses that character in the formula.
  • You should use .* after the character in the formula so that the function removes all characters after that.
  • You can also use *. before the character in the formula to remove all characters before that.

Conclusion

We have shown 4 different ways to remove everything after the character in Google Sheets. Now you can apply these methods to your dataset. If you have any queries or suggestions, please let us know in the comment section below. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.


Related Articles

Afrina

Afrina

This is Afrina Nafisa. Currently working as a Technical Writer at OFFICEWHEEL.COM. I have done my graduation from Ahsanullah University of Science and Technology. As I am eager to learn more and more currently my motive is to make myself better every day with my work so that I can make better content for all the readers.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo