Skip to main content

How To Remove First, Last X Characters Or Certain Position Characters From Text In Excel?

Author: Tech Support Last Modified: 2023-01-16
For most of Excel users, sometimes, you need to delete first n characters from the beginning of the text strings or remove the last x characters from the end of the text strings as below screenshot shown. This article, I will introduce some handy tricks for solving this task quickly and easily in Excel.
 

doc remove first x characters 1


Method 1: Remove first or last x characters from text strings with formulas

 Remove first x characters from the beginning of the text strings:

In Excel, you can use the RIGHT and LEFT functions to remove the certain number of characters from the beginning or the end of the strings, please do as this:

1. Type or copy the following formula in a blank cell C4 where you want to put the result:

=RIGHT(A4, LEN(A4)-2)

and press Enter key to get the first result. See screenshot:

doc remove first x characters 2

Note: In the above formula:

  • A4 is the cell value that you want to remove characters;
  • The number 2 means the number of characters you want to remove from the beginning of the text string.

2. Then, select the cell C4 and drag the fill handle down to the cells where you want to apply this formula, and all the first 2 characters have been removed from the text strings, see screenshot:

doc remove first x characters 3


 Remove last x characters from the end of the text strings:

If you need to remove the last several characters, you can use the LEFT function as the same as the RIGHT function.

Please enter or copy this formula into a blank cell:

=LEFT(A4, LEN(A4)-9)

and then drag the fill handle down to the cells that you want to apply this formula, and last 9 characters have been deleted from the text strings at once, see screenshot:

doc remove first x characters 4

Note: In the above formula:

  • A4 is the cell value that you want to remove characters;
  • The number 9 means the number of characters you want to remove from the end of the text string.

Method 2: Remove first or last x characters from text strings with User Defined Function

Here is a User Defined Function which also can help you to remove first or last n characters from text strings, please do as this:

 Remove first x characters from the beginning of the text strings:

1. Hold down the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

Public Function removeFirstx(rng As String, cnt As Long)
removeFirstx = Right(rng, Len(rng) - cnt)
End Function

3. Then go back to the worksheet, and then enter this formula: =removefirstx(A4,2) into a blank cell, and then drag the fill handle down to get the results as you need, see screenshot:

doc remove first x characters 5

Note: In the above formula:

  • A4 is the cell that you want to remove characters;
  • The number 2 indicates the number of characters you would like to remove from the beginning of the text string.

 Remove last x characters from the end of the text strings:

To remove last n characters from the text strings, please apply the following User Defined Function:

Public Function removeLastx(rng As String, cnt As Long)
removeLastx = Left(rng, Len(rng) - cnt)
End Function

And then apply this formula: =removelastx(A4,9) to get the results as you need, see screenshot:

doc remove first x characters 6

Note: In the above formula:

  • A4 is the cell that you want to remove characters;
  • The number 9 indicates the number of characters you would like to remove from the end of the text string.

Method 3: Remove first, last x characters or certain position characters without any formulas

Using the Excel functions to remove certain characters is not as directly as it is. Just take a look at the way provided in this method, which is no more than two or three mouse clicks. With the Remove by Position utility of the third party add-in Kutools for Excel, you can be easy to remove first, last or certain characters from the text string. Click to download Kutools for Excel! Please see below demo:

After installing Kutools for Excel, please apply Remove by Position according to these steps:

1. Select the range that you want to remove the certain characters. Then click Kutools > Text > Remove by Position. See screenshot:

2. Specify the following operations in the pop-up Remove by Position dialog box.

  • (1.) Specify the number of characters to be deleted.
  • (2.) Choose From left option under the Position section to delete the first n characters, see screenshot:

doc remove first x characters 8

Tips: The Remove by Position utility also can help you to remove the last n characters or the certain characters from specific position.

Method 4: Remove both first x and last x characters from text strings with formula

Sometimes, you would like to remove characters from text strings on both sides, for example, you need to remove first 2 characters and last 9 characters at the same time. Here, the MID function can do you a favor.

1. Please enter or copy this formula into a blank cell:

=MID(A4,3,LEN(A4)-11)

and press Enter key to get the first result. See screenshot:

doc remove first x characters 11

Note: In the above formula:

  • A4 is the cell value that you want to remove characters;
  • The number 3 is one more than the number of characters you want to remove from the left side;
  • The number 11 is the total number of characters you want to remove.

2. Then, select the cell C4 and drag the fill handle down to the cells where you want to apply this formula, and all the first 2 and last 9 characters have been removed immediately from the text strings, see screenshot:

doc remove first x characters 12


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 Toolsets12 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 (134)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have a problem. I need to remove specific last character in the sheet. Example: I have people names and I need to remove the last character, if it is "a", but that the rest of the name contains the letter "a". How I can do that?
This comment was minimized by the moderator on the site
i want remove the last part ex: (jaison kkrrt po kizha) like an adress want edit only jaison
is it possibile?
This comment was minimized by the moderator on the site
In the nursery (plant) industry, we have a lot of plants that are crosses and have an x after the Genus (1st word). Is there a way to NOT have the x alphabetized? A couple example names: Buxus x 'Green Mountain' and Thuja x 'Green Giant'. These names are constantly at the end of the listing of Buxus and Thuja instead of being near the top. By the way, it's always "space x space". Can there be a way to highlight a column and tell it to ignore the x if proceeded and followed by a space when alphabetizing the names in that column? Thank you.
This comment was minimized by the moderator on the site
Wow! Kutools berfungsi dengan sempurna di Ms. Office Pro Plus 2016. Sungguh menolong saya, menghemat waktu dalam menghapus karakter tertentu di data excel saya. Terima kasih banyak.
This comment was minimized by the moderator on the site
How can I get rid of the last value in a cell IF it is the letter 'N'? Not all have the 'N' at the end and I want to only remove those that do?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations