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

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

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

Press Enter to see the names only.

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)

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.

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.

This is the output.

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)

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)

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 LEFT, FIND and SUBSTITUTE functions delete everything after the last comma (as shown in Method 5).

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

To delete everything but the names:

Steps:

Select the dataset (B5:B9).

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

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