How to Delete Everything After a Character in Excel -7 Methods

Method 1 – Applying the Find and Replace Option to Delete Everything After a Character in Excel

Steps:

  • Select the dataset (B5:B9).

Apply Find and Replace Option to Delete Everything After a Character in Excel

  • Press Ctrl+H.
  • Select Replace and enter ‘,*’ in ‘Find what’.
  • Leave ‘Replace with’ blank.
  • Click Replace All.

Apply Find and Replace Option to Delete Everything After a Character in Excel

  • All characters after the name will be deleted.


Method 2 – Remove Everything After a Character Using the Flash Fill

Steps:

  • Enter only the name in C5.
  • Start to write the name in C6 and you will see that Excel recognizes a pattern (keep names).

Remove Everything After a Character Using Flash Fill

  • Press Enter to see the names only.

Read More: How to Remove Text After Character in Excel


Method 3 – Combining the LEFT and the SEARCH Functions to Delete Everything After a Character in Excel

Steps:

  • Enter the following formula in C5.
=LEFT(B5,SEARCH(",",B5)-1)

Combination of LEFT and SEARCH Functions to Delete Everything After a Character in Excel

This is the output.

  • Drag down the Fill Handle to see the result in the rest of the cells.

 Formula Breakdown

SEARCH(“,”,B5)

returns the position of the character (here, comma).

SEARCH(“,”,B5)-1)

one character is subtracted from the number returned by SEARCH to exclude the comma(,).

LEFT(B5,SEARCH(“,”,B5)-1)

returns the specified number of characters from the beginning of the string.

Read More: How to Remove Text between Two Characters in Excel


Method 4 – Remove Everything After a Character Using the Combination of the LEFT and the FIND Functions in Excel

Steps:

  • Enter the following formula in C5.
=LEFT(B5,FIND(",",B5)-1)

The FIND function returns the position of ‘,’ . The rest of the formula is similar to the formula described in Method 3.

Remove Everything After a Character Using the Combination of LEFT and FIND Functions in Excel

  • This is the output.

Remove Everything After a Character Using the Combination of LEFT and FIND Functions in Excel


Method 5 – Delete Everything After the Nth Occurrence of a Character in Excel

The data string contains several commas (Adelle, Engineer, CEO, 2232). To discard everything after the 2nd comma:

Steps:

  • Enter the following formula in C5.
=LEFT(B5,FIND("#",SUBSTITUTE(B5,",","#",2))-1)

Delete Everything After Nth Occurrence of a Character in Excel

This is the output.

 Formula Breakdown

SUBSTITUTE(B5,”,”,”#”,2)

The SUBSTITUTE function replaces the 2nd comma with ‘#’’.

FIND(“#”,SUBSTITUTE(B5,”,”,”#”,2))

returns the position of the 2nd comma: 17th.

FIND(“#”,SUBSTITUTE(B5,”,”,”#”,2))-1)

one character is subtracted from the number returned by the previous part of the formula.

LEFT(B5,FIND(“#”,SUBSTITUTE(B5,”,”,”#”,2))-1)

returns the specified number of characters from the beginning of the string.


Method 6 – Remove Everything After the Last Occurrence of a Character in Excel

To delete everything after the last comma in: Adelle, Engineer, CEO, 2232:

Steps:

  • Enter the following formula in C5.
=LEFT(B5,FIND("#",SUBSTITUTE(B5,",","#",LEN(B5)-LEN(SUBSTITUTE(B5,",",""))))-1)

Remove Everything After Last Occurrence of a Character in Excel

Everything is deleted after the last comma.

  • Drag down the Fill Handle to see the result in the rest of the cells.

 Formula Breakdown

LEN(SUBSTITUTE(B5,”,”,””))

determines the position of the last delimiter (last comma) and replaces each comma with a blank (“”).  The LEN function returns the length of the string: 24 for B5.

LEN(B5)-LEN(SUBSTITUTE(B5,”,”,””))

the result of the previous part is subtracted from the original total length of B5. The result is 3: the number of commas in B5.

The LEFTFIND and SUBSTITUTE functions delete everything after the last comma (as shown in Method 5).

Read More: How to Remove Letters from Cell in Excel


Method 7 – Deleted Everything After a Certain Character Using VBA in Excel

To delete everything but the names:

Steps:

  • Select the dataset (B5:B9).

Erase Everything After a Certain Character Using VBA in Excel

  • Right-click the sheet and choose ‘View Code’.

Erase Everything After a Certain Character Using VBA in Excel

  • A VBA Module window will open. Enter the following code and Run it.
Option Explicit

Sub remove_everything_after_char()

Dim rng As Range
Dim cell As Range
Set rng = Application.Selection
For Each cell In rng
cell.Offset(0, 1).Value = Left(cell, InStr(cell, ",") - 1)
Next cell

End Sub

Erase Everything After a Certain Character Using VBA in Excel

Only names are displayed.


Download the Practice Workbook

Download the practice workbook.


Related Articles

<< Go Back To Excel Remove Text | Data Cleaning in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo