Skip to main content

Explore the new, free version of Kutools for Outlook today!

How to delete string after nth character in Excel?

Author: Sun Last Modified: 2024-07-24

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.

screenshot of deleting characters after the nth character

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:

screenshot of deleting characters after the nth character using formula

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)
screenshot of deleting characters after the nth character using formula 2
Tip: In the formula above, you can replace the number 5 with any other number to specify how many characters you want to keep. For example, changing it to 7 will remove all characters after the 7th character in the text string.

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 Positionto 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
    screenshot of deleting characters after the nth character using kutools
Result

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

result screenshot of deleting characters after the nth character using kutools
Note: To use this feature, you should have Kutools for Excel installed on your computer. Go to download Kutools for Excel to get a 30-day free trial with no limitations.

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

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

Description

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!
Comments (4)
No ratings yet. Be the first to rate!
sunil
Guest
This comment was minimized by the moderator on the site
data UPI-2080-SHN ANNY SMI-KKBK-XXXXXX-MB: SOCIETY MAINTENANCE MARCH 22 0000####### 01/03/22
Output :- SHN ANNY SMI
Reply
0
0
Sunny
Sunny     sunil
This comment was minimized by the moderator on the site
Hi, sunil, here is a code may help you. 
Sub extractText()
    Dim xSplit, xStr As String
    Dim xPos As Integer
    Dim xArr As Variant
    Dim xRng, xSetRng As Range
    On Error Resume Next
    Set xRng = Application.InputBox("Select the cell you want to extract：", "Kutools for Excel", , , , , , 8)
    xSplit = Application.InputBox("Type the delimiter：", "Kutools for Excel", , , , , , 2)
    xPos = Application.InputBox("Type the nth delimiter：", "Kutools for Excel", , , , , , 1)
    Set xSetRng = Application.InputBox("Select a cell to place result：", "Kutools for Excel", , , , , , 8)
    xArr = Split(xRng.Text, xSplit)
    xSetRng.Value = xSplit + xArr(xPos)
End Sub

Copy and paste above code to the Microsoft Visual Basic for Applications window, and run the code, in first dialog, choose the cell that you use to extract, in second dialog, type the delimiter, here in your case is -, in third dialog, type the nth delimiter, here in your case, you extract the string after second -, type 2 into this dialog, in last dialog, choose the cell you place the extracted result.
Hope it do favor on you.
Reply
Report
0
0
sunil
Guest
This comment was minimized by the moderator on the site
data :- UPI-205080-SWN AHONY SI-KKBK-XXXXXX-MB: SOCIETY MAINTENANCE MARCH 22 000080 01/03/22
Output needed as "SWN AHONY SI"
Reply
0
0
Kelly
Guest
This comment was minimized by the moderator on the site
These are very clever tricks! Thank you so much.
Reply
0
0
There are no comments posted here yet
Leave your comments
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations