Maruf
Assume you have obtained a raw dataset in Google Sheets. Each value in the dataset contains various extra characters after a specific character. Now you want to extract the required data by removing the extra characters. In case you have a large dataset, doing this manually is never a suitable way to do that. In this article, we will highlight how you can easily remove everything after the character using the formula in Google Sheets. Follow the methods below to be able to do it all by yourself to get the desired result.
A Sample of Practice Spreadsheet
You can download the practice spreadsheet from the download button below.
5 Ways to Remove Everything after Character Using Formula in Google Sheets
The following dataset contains some user ID Codes. The IDs contain the first names of the users and a unique code separated by an underscore (“_”) character. Assume you need to remove everything after the (_) character to extract the names from the codes as shown below.
In Google Sheets, there are several ways for you to remove everything after any specific character using a formula. Follow the methods below to learn about them.
1. Applying REGEXREPLACE Function
You can apply the REGEXREPLACE function to remove everything after the character. This function replaces texts with another text or removes text before or after certain characters. Follow the steps to do so.
- First, select cell C5 and insert the formula mentioned below. Then, drag the Fill handle icon below to copy down the formula.
=REGEXREPLACE(B5,"_.*","")
- Here, B5 contains the reference text. “_.*” means you want to replace everything after the character “_”. Notice that the _ character is followed by a dot (.) and an asterisk (*). You need to keep the replacement text empty (double quotes only) to remove everything after the character.
2. Utilizing LEFT and FIND Functions
You can also use the LEFT and FIND functions to remove everything after the character too. Here the LEFT function returns a substring from the beginning of a string. Follow the steps below to see how to apply that.
- First, select cell C5 and apply the following formula. Then drag down the fill handle or copy the formula to other cells of the column.
=LEFT(B5, FIND("_", B5)-1)
- As a result, you will get the whole column without the values after the character.
Read More: How to Remove Last Character from String in Google Sheets
3. Using LEFT and SEARCH Functions
Alternatively, you can also use the LEFT and SEARCH functions in case the dataset contains the specified character with upper and lower cases.
- First, select cell C5 and enter the formula below. Then drag the fill handle icon to copy the formula to the other cells in the column.
=LEFT(B5, SEARCH("_", B5)-1)
- Here the SEARCH function finds the position of the character in the reference text in cell B5. -1 reduces the position by 1 to exclude the specific character. The LEFT function returns all characters from the beginning up to that position.
4. Applying REGEXEXTRACT Function
Another easy way to get that result is to apply the REGEXEXTRACT function. Follow the steps below to remove every after character using this formula in google sheets.
- First, select cell C5 and insert the formula given below. Then copy down the formula using the Fill handle icon.
=REGEXEXTRACT(B5,"(.*)_.*")
- Here, the REGEXEXTRACT function extracts the substring, and B5 is the cell you want to extract the substring from. “(.*)_.*” means it will extract values before the character _ and will remove the rest data.
5. Utilizing SPLIT Function
You can also utilize the SPLIT function in google sheets to get the same result as earlier. It splits any text by a specific character or delimiter.
- First, enter the below formula in cell B5. Then copy the formula below.
- This will split the data with characters into two parts just like the following image. Here, data before and after the character “_” are split into column C and column D.
=SPLIT(B5,"_")
- Next, to remove the unnecessary data from column D, you need to copy the values of C5:C10 and paste them as values. Then the values of D5:D10 will be automatically deleted.
How to Remove Everything after Character Without Using Formula in Google Sheets
Instead of the above formulas, you can apply the simple Find and replace feature in Google Sheets. Then, you will be able to clear everything after the character without applying any formula! Follow the below steps to do that.
Steps:
- First copy the data of cell B5: B10 to cell C5:C10.
- Next, select Edit from the menu, and a drop-down will appear. Then select the Find and replace option.
- Put the character with a dot and an asterisk (“_.*”) in the Find box and leave the Replace with box empty if you want to remove everything.
- Check the same boxes shown in the image. After that select Replace All and click the Done button.
- Finally, you will get a dataset like the following one!
Read More: How to Remove Everything after Character in Google Sheets
Things to Remember
- You can also remove everything before a character too. Just change the “.*” to “*.” for doing that.
- You can remove everything after the formula or you can delete the character also. To do that just put the character in the formula or add -1 in a formula like shown in this article.
Conclusion
In this article, we tried to show simple formulas to remove everything after a character in Google Sheets. Hope you understand the methods easily and apply them when necessary. Please use the comment section below for further queries or suggestions. Hopefully, it will help you to visualize your data more properly. You may also visit our OfficeWheel blog to explore more about Google Sheets.
