This article introduces different methods of removing texts before or after a specific character or the nth occurrence character from cells in Excel. Show
Method A: Delete texts before or after specific character by Find and Replace in ExcelFor removing all texts before or after a specific character with the Find and Replace function, please do as follows. 1. Select the cells you will remove texts before or after a specific character, press Ctrl + H keys to open the Find and Replace dialog. In the Find and Replace dialog box: 1. For removing all before the specific character such as comma, please type *, into the Find what text box; 2. For removing all after the specific character such as comma, please type ,* into the Find what text box; Notes: 1. You can change the comma to any character as you need. 2. In this case, all texts before the last comma or after the first comma will be removed from the selected cells. 2. Keep the Replace with text box empty, and then click the Replace All button. See screenshot: Easily remove all numeric, non-numeric or specified characters from cells in Excel Kutools for Excel's Remove Character utility helps you easily remove all numeric, non-numeric or specified characters from selected cells in Excel. Delete texts before or after the first/last specific character by formulaThis section will show you formulas of deleting everything before or after the first/last specific character from cells in Excel. To remove everything before the first comma, please: Select a blank cell, copy and paste the below formula into it, and press Enter key. Then drag the Fill Handle to apply the formula to other cells. See screenshot: Formula: Remove everything before the first comma =RIGHT(B5,LEN(B5)-FIND(",",B5)) Notes: 1. In the above formula, B5 is the cell you will remove texts from, and "," is the character you will remove texts based on. 2. To remove all before the last specific character, use this formula: =RIGHT(B5,LEN(B5)-FIND("@",SUBSTITUTE(B5,"Character","@",(LEN(B5)-LEN(SUBSTITUTE(B5,"Character","")))/LEN("Character")))) To remove everything after the first comma, please: Select a blank cell, copy and paste the below formula into it, and press Enter key. Then drag the Fill Handle to apply the formula to other cells. See screenshot: Formula: Remove everything after the first comma =LEFT(B5,FIND(",",B5)-1) Notes: 1. In the above formula, B5 is the cell you will remove texts from, and "," is the character you will remove texts based on. 2. To remove all after the last specific character, use this formula: =LEFT(B5,FIND("@",SUBSTITUTE(B5,"character","@",LEN(B5)-LEN(SUBSTITUTE(B5,"character",""))))-1) Delete texts before or after the nth occurrence character by formulaThe below formulas can help to delete all before or after the nth occurrence character from cells in Excel. To remove all before the nth occurrence character from cells, you need to: Select a blank cell to output the result, copy the below formula into it, and press Enter key. Then drag the Fill Handle to apply the formula to other cells. See screenshot: Formula: Remove everything before the second occurrence comma =RIGHT(SUBSTITUTE(B5, ",", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, ",", CHAR(9), 2), 1) + 1) Notes: 1. In the formulas, B5, "," and 2 number mean that all contents after the second occurrence comma will be removed from cell B5. 2. You can change the "," and 2 number to any character and occurrence position number as you need. To remove all after the nth occurrence character from cells, you need to: Select a blank cell to output the result, copy the below formula into it, and press Enter key. Then drag the Fill Handle to apply the formula to other cells. See screenshot: Formula: Remove everything after the second occurrence comma =LEFT(SUBSTITUTE(B5,",",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5,",",CHAR(9),2),1)-1) Notes: 1. In the formulas, B5, "," and 2 number mean that all contents after the second occurrence comma will be removed from cell A7. 2. You can change the "," and 2 number to any character and occurrence position number as you need. Easily remove texts before/after a specific character with Kutools for ExcelIf there is only one comma separator for each cell in a range, and you want to remove everything before or after this comma from cells, please try the Split Cells utility of Kutools for Excel. This utility will help you to solve the problem with only several clicks: 1. Select the cells which you will remove everything before or after the comma from, and then click Kutools > Text > Split Cells. See screenshot: 2. In the Split Cells dialog, select the Split to Columns option in the Type section, and in the Split by section, choose the Other option and type a comma into the blank box, and then click the OK button. See screenshot: 3. Another Split Cells dialog pops up, select a blank cell for locating the texts, and then click the OK button. Then you can see the selected cells are split by specific character – comma. See screenshot: If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps. Kutools for Excel - Helps You Always Finish Work Ahead of Time, Have More Time to Enjoy Life Do you often find yourself playing catch-up with work, lack of time to spend for yourself and family? Kutools for Excel can help you to deal with 80% Excel puzzles and improve 80% work efficiency, give you more time to take care of family and enjoy life. 300 advanced tools for 1500 work scenarios, make your job so much easier than ever. Oldest First Sort comments by Oldest First Newest First Comments (60) No ratings yet. Be the first to rate! Sean about 8 years ago This comment was minimized by the moderator on the site Fantastic, thanks for that! So easy Reply 0 0 Bill Liew about 7 years ago This comment was minimized by the moderator on the site A big thank you. Perfect solution. Reply 0 0 willie gluck about 7 years ago This comment was minimized by the moderator on the site Some great tips there! thanks. Reply 0 0 Vinoda about 7 years ago This comment was minimized by the moderator on the site It is too helpfull . Reply 0 0 Mohammed about 7 years ago This comment was minimized by the moderator on the site So grateful to you, that was helpful Reply 0 0 Mariela about 7 years ago This comment was minimized by the moderator on the site I like this way better than the mid formula! Reply 0 0 Otep about 7 years ago This comment was minimized by the moderator on the site Thank you for this! Got to save some precious time for a 600 line item. Reply 0 0 Ken about 5 years ago This comment was minimized by the moderator on the site The formula for deleting text after a character is exactly what I needed. Thank you! Reply 0 0 Barnett Frankel about 5 years ago This comment was minimized by the moderator on the site I need to delete all text after the first word. Reply 0 0 crystal Barnett Frankel about 5 years ago This comment was minimized by the moderator on the site Dear Barnett Frankel, Reply Report 0 0 Tom about 5 years ago This comment was minimized by the moderator on the site This was very useful indeed to help me create / extract new logins from our email database - thank you! (and thanks, Excel)! Reply 1 0 TarunKumar about 5 years ago This comment was minimized by the moderator on the site Please share the formula for finding multiple spaces in a text string, to extract what we wish from that string, easily. Reply 0 0 crystal TarunKumar about 5 years ago This comment was minimized by the moderator on the site Dear TarunKumar, Reply Report 0 0 Atul about 5 years ago This comment was minimized by the moderator on the site I have multiple / in my string and want to separate the text or string after the last / found in the string, please tell me how to do this How do you remove text before or after a specific character in Google Sheets?RIGHT+LEN+FIND. There are a few more Google Sheets functions that let you remove the text before a certain character. They are RIGHT, LEN and FIND.
How do I trim characters in Google Sheets?TRIM is a function in Google Sheets that removes all spaces from text except for single spaces between words. This can be useful when cleaning up data or text that has been copied and pasted from other sources. The TRIM function can be accessed by typing =TRIM( in the cell where you want the text to be cleaned up.
How do you replace everything after a character in sheets?To use REPLACE in Google Sheets, you simply need to type =REPLACE( into the cell where you want to perform the replacement, and then input the text you want to replace, the text you want to replace it with, and the number of times you want it to occur.
How do I remove spaces after text in Google Sheets?Remove extra spaces. On your computer, open a spreadsheet in Google Sheets.. Select the data range that you'd like to remove extra leading, trailing, or excessive spaces in.. At the top, click Data Data cleanup. Trim whitespace.. |