How to delete string after nth character in Excel?

In Excel, you might often need to manipulate text strings by removing characters after a specific position. For example, you might want to truncate a string after the nth character to standardize data formats or clean up imported text. This guide will explore two effective techniques for deleting characters after the nth position in a string: using formulas and Kutools for Excel.

Remove all characters after the nth character with formula

Formulas offer a versatile way to handle text manipulation tasks in Excel, including removing characters after a specific position. This method is useful when you need a dynamic solution that adjusts automatically as your data changes. Here’s how you can use a formula to delete all characters after the nth character in a string:

Assuming you want to delete everything after the 5th character from the data in the following screenshot, here's how to handle it:

Select a blank cell and enter the following formula, press the "Enter" key and drag the "Fill Handle" of this result cell down to get the rest of the results.

=LEFT(A1,5)

Easily remove the specified number of characters after the nth character

"Kutools for Excel" offers a more user-friendly approach for removing characters after a certain position, especially useful when dealing with large datasets or when you prefer a tool-based solution. This method simplifies the task with just a few clicks. Here’s how you can use Kutools for Excel to achieve this:

After downloading and installing Kutools for Excel, go to the "Kutools" tab and select "Text" > "Remove by Position" to open the dialog box, then configure as follows:

1. In the "Numbers" text box, enter the number of characters you want to remove.
   Tip: If you want to remove all characters after the nth character, you can enter a very large number, such as 999, to ensure that all characters following the nth character are removed.
2. Select the "Specify Start Position" option, then enter the number representing the position from which you want to start removing characters.
   In this case, to remove characters after the 5th character, enter the number 6. This specifies that character removal will start from the 6th character.
3. Click "OK".

   

Result

Then, the specified number of characters after the 5th character will be removed from each cell in the selected range.