Note: The other languages of the website are Google-translated. Back to English

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

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



The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (129)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
its use full to make data much easier Thanks
This comment was minimized by the moderator on the site
Sorry, but doesn't work for me. Shows me an error.
This comment was minimized by the moderator on the site
Print this item to help with removing characters.
This comment was minimized by the moderator on the site
thank u dear for this help
This comment was minimized by the moderator on the site
Thank you for posting this!! :-)
This comment was minimized by the moderator on the site
this is what i am looking for.. I need one more thing: I want to copy the values which are in brackets into another cell, in your case- Postcode (ZIP) : 211230, want to copy ZIP into another cell.
This comment was minimized by the moderator on the site
[quote]this is what i am looking for.. I need one more thing: I want to copy the values which are in brackets into another cell, in your case- Postcode (ZIP) : 211230, want to copy ZIP into another cell.By Happy[/quote] Hi, Happy. Did you ever get an answer? If not, easiest way would be with the Mid command. For example, if Postcode (ZIP): 211230 is in Cell A1, the command would be =MID(A1,11,3) - i.e., take the middle 3 characters starting with the 11th one from the left.
This comment was minimized by the moderator on the site
I have 275 names but in a repeated manner. I want to extract each name from that list which will appear only once.
This comment was minimized by the moderator on the site
Hi Zedd, For the task mentioned by you. We can use IF and COUNT IF combination Ex: =IF(COUNTIF($A$1:$A$275,A1)= 1,A1,FALSE) Regards, Umakanth Ramineedi
This comment was minimized by the moderator on the site
select the specific column and in "data" panel.. click "remove duplicates"
This comment was minimized by the moderator on the site
Thaks for helping me
This comment was minimized by the moderator on the site
Excellent tip! You guys just made my life so much easier :)
This comment was minimized by the moderator on the site
THanks a lot was really helpful!
This comment was minimized by the moderator on the site
Great tip! Very helpfull for beginners like me! Thanks!
This comment was minimized by the moderator on the site
Thank you team .... really helpful
This comment was minimized by the moderator on the site
I have a group of numbers. Some of them have a zero at the beginning. I can't have a zero as the first number. The numbers are not all the same length but I just want the zero gone. Is there a way to do that.
This comment was minimized by the moderator on the site
@kn : you could use the following, considering the source cell is A1 In the formula below, if will evaluate what the first character is, and if it's a zero, it will remove it, if it is not a zero, it will remain as is. =IF((LEFT(A1,1)="0"),RIGHT(A1,(LEN(A1)-1)),A1) hope this helps, cheers!
This comment was minimized by the moderator on the site
Hi! Hope you can help me guys how to remove any characters after first comma from Left and add
This comment was minimized by the moderator on the site
Hi #Ray, Your question seems like it's missing some details at the end, but to remove anything after the first coma, you can use the following: assuming your source data is in A1. Basically, grabs everything left of the first coma it finds in the string (position of the coma minus 1) =LEFT(A1,(SEARCH(",",A1)-1)) if A1 is abcdef,ghijkl then you will get abcdef
This comment was minimized by the moderator on the site
Hi guys, set up a list with 3 columns and about 500 rows, and in every cell I have a"#" that has to be removed. I really dont understand how to remove it and dont get on with the formula on top. May it be because I'm using a mac device?
This comment was minimized by the moderator on the site
just do a find and replace. Find "#" replace with "" blank. That way you don't even need a formula.This is considering there is only one "#" you'd like to remove in each cell. cheers!
This comment was minimized by the moderator on the site
but what if the characters in a cell have different font colors and I want to retain their colors?
This comment was minimized by the moderator on the site
Use the format painter in that case. Suppose your original data is in 'column A' (which is formatted with color) and the data after applying formula is in 'column B' (unformatted) then follow the following steps: 1) Click on 'Format Painter' from 'Home' menu 2) Click on column name 'A' (complete column should be selected) 3) Click on column name 'B' You will have same formatting for column B as your source column (column A). Hope this helps.
This comment was minimized by the moderator on the site
Nice tip guys. Very useful tips
This comment was minimized by the moderator on the site
Thank you! The formula works for my needs! :-)
This comment was minimized by the moderator on the site
thanks, this is a great post. I am trying to pull the first letter of two names to automate into a action log? Can you help me with this formula? Sure it is LEN. But can't just get it.... eg. Brian Adams = BA Thanks Craig
This comment was minimized by the moderator on the site
@Craig, you could do this : =CONCATENATE((MID(A2,1,1)),(MID(A2,(FIND(" ",A2)+1),1))) considering your source data is in cell A2. However this will not work if you have more than 2 distinct names separated by spaces. You could elaborate that in a IF statement to tackle cases where you have 3 names and want to extract 3 letters (i.e. John Bon Jovi....JBJ) cheers!
This comment was minimized by the moderator on the site
try this bro A1=Brian Adams B2=CONCATENATE(LEFT(A1,1),LEFT(RIGHT(A1,SEARCH(" ",A1)-1),1))
This comment was minimized by the moderator on the site
Awesome! This was very helpful, thanks!!!
This comment was minimized by the moderator on the site
really helpful Thank you for posting this
This comment was minimized by the moderator on the site
the futnction was very helpful and because of this i could save my lagr amount of time
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations