Afrina
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.
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.
- 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,"/.*","/")
- 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))
- 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))
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.
