Skip to main content

How to remove texts before or after a specific character from cells in Excel?

Author: Sun Last Modified: 2024-11-12

This article introduces different methods of removing texts before or after a specific character or the nth occurrence character from cells in Excel.

remove texts before or after a specific character

Delete texts before or after specific character by Find and Replace in Excel

For 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:

specify options in the Find and Replace dialog box

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.
Download the full feature 30-day free trail of Kutools for Excel now!

remove all numeric, non-numeric or specified characters by kutools

Delete texts before or after the first/last specific character by formula

This 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"))))

remove everything before the first comma

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)

remove everything after the first comma

Delete texts before or after the nth occurrence character by formula

The 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.

remove all before the nth occurrence character

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.

remove all after the nth occurrence character

Easily remove texts before/after a specific character with Kutools for Excel

If 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:

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select the cells which you will remove everything before or after the comma from, and then click Kutools > Text > Split Cells. See screenshot:

click Kutools > Text > Split Cells

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:

specify the options in the dialog box

3. Another Split Cells dialog pops up, select a blank cell for locating the texts, and then click the OK button.

select a blank cell for locating the texts,

Then you can see the selected cells are split by specific character – comma. See screenshot:

he selected cells are split by specific character

  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.
No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
Complicated and repeated operations can be done a one-time processing in seconds.
Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
Become an Excel expert in 3 minutes, help you quickly get recognized and a pay raise promotion.
110,000 highly effective people and 300+ world-renowned companies' choice.
Make your $39.0 worth more than $4000.0 training of others.
Full feature free trial 30-day. 60-Day Money Back Guarantee without reason.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!