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

#### 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:

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:

###### 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:

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:

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:

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:

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:

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:

### Best Office Productivity Tools

Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel

 Popular Features: Find/Highlight/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   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ... 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 Toolset:  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, ...)   |   Many More...

Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...

Supports Office/Excel 2007-2021 & newer, including 365   |   Available in 44 languages   |   Enjoy a full-featured 30-day free trial.

#### 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!
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?
This comment was minimized by the moderator on the site
How can I get this number reduced using formulas to make 3604000000157477 to be 3604157477 first 4 to the front and last 6 digits.
This comment was minimized by the moderator on the site
Tengo una ruta en una columna ejm C:\Users\jaja\Downloads\aa.txt. Necesito eliminar todos los carácteres que están después del último slash
This comment was minimized by the moderator on the site
Hi I have a data with names and surnames of some people, i want to separate surname in coloumn and name in another coloumn, surnames are not same like michel, john, daniel, williams like please help me to separate surname by using formula, I have lot of data
This comment was minimized by the moderator on the site
Hi, Giridhar,
Maybe the below article can solve your problem, please try, thank you!
https://www.extendoffice.com/documents/excel/829-excel-split-first-last-name.html
This comment was minimized by the moderator on the site
If number or character differ you can use example below (I needed to remove the last character):
=PART(A1;1;(LENGTH(A1)-1))
This comment was minimized by the moderator on the site
I WANT TO TRIM TEXT LENGTH, FOR EXAMPLE I HAVE SOME ADDRESS THAT LENGTH SOME COLUM 10 CHARECTOR AND SOME COLUM 20 CHARECTOR SOME 15 CHARECTOR IN THIS CASE I WANT TO TRIM WHICH COLUM TEXT MORE THAN 15 I WANT TO TRIM THAT COLUM TEXT TO 15 CHARECTOR, DONT TRIM OTHER COLUM TEXTS

ANYBODY HELP ME
This comment was minimized by the moderator on the site
Use "text to column"
This comment was minimized by the moderator on the site
Very helpful tip. Something we use a lot in our environment.
This comment was minimized by the moderator on the site
I gave function error when I apply formula to cell. After 2 hours I try it again by using (;) instead of (,) and result positive. As result of;
=removelastx(E4,3) (not working)
=removelastx(E4;3) (working)
This comment was minimized by the moderator on the site
How to remove the first character from a cell but not in every cell in that column? Thanks!
This comment was minimized by the moderator on the site
This helped to get the result from an entry but I am unable to LOOKUP the new value.
This comment was minimized by the moderator on the site
This really helps me a lot in my daily working.

Thanks
This comment was minimized by the moderator on the site
Hi,
I have the formula that gives me the #value error that I don't seem to be able to figure out how to fix it.

This comment was minimized by the moderator on the site
How can I type the command to cut text from the left and the right?
This comment was minimized by the moderator on the site
I can't make this formula work, I type : =LEFT(A1,LEN(A1)-4) and it gives error everytime.
My A1 column has the following data: ELDB-AK-S-42228-001

Why isn't this working?
This comment was minimized by the moderator on the site
i have a list of sizes that have numbers and names, words,etc. i want to remove all letters in the entire column leaving only the numbers. how do i remove all letter which come in hundreds of different variations and lengths?
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
Hi there everyone,
I am trying to remove an initial in a field that includes a persons name. An example is

Taylor Jeremy D In this example I am trying to remove the D and just leave surname and first name there.

The problem is that I have a heap of data which is inconsistently entered meaning that some of the fields have an initial (like that above) and some just say a name like Taylor Jeremy (without an initial).

Any ideas so that I can have a consistent data set and one that excludes the initial?
This comment was minimized by the moderator on the site
Try this, it solves exactly the way you want it.

=IF((IFERROR((FIND(" ",A1,((FIND(" ",A1,1))+1)))/(FIND(" ",A1,((FIND(" ",A1,1))+1))),0))=1,LEFT(A1,((FIND(" ",A1,((FIND(" ",A1,1))+1)))-1)),A1)

A1 is Taylor Jeremy D or without the D, as you wish
This comment was minimized by the moderator on the site
Absolutely a genius! THANKS A LOT! garza01
This comment was minimized by the moderator on the site
Just for lettiing you know that I solved your problem, I hope im not too late.

=SI((SIERREUR((TROUVE(" ";R9;((TROUVE(" ";R9;1))+1)))/(TROUVE(" ";R9;((TROUVE(" ";R9;1))+1)));0))=1;GAUCHE(R9;((TROUVE(" ";R9;((TROUVE(" ";R9;1))+1)))-1));R9)

Cell R9 is your Taylor Jeremy D, try it with and without the D and you'll see it works fine
This comment was minimized by the moderator on the site
If the text name (Taylor Jeremy D) located in A1, you can use below function to acheive the result (remove the last name):
=LEFT(A1,FIND(" ",A1,1))&LEFT(RIGHT(A1,LEN(A1)-A2),FIND(" ",RIGHT(A1,LEN(A1)-A2))-1)
This comment was minimized by the moderator on the site
Can also be accomplished with the below string, which is simpler and doesn't require formulas in multiple cells. Following same assumption that data is in A1 and the initial is always 1 character. TRIM function added within the LEN function in case there are unseen trailing spaces in the data. TRIM function added at the beginning of the formula will delete any residual spacing after the LEFT function is executed.

=TRIM(LEFT(A1,LEN(TRIM(A1))-1))

Or if you don't like the leading TRIM function you can subtract 2 characters from the LEN function instead of just the one. I prefer TRIM route in case there are more than 1 spaces in between the name and middle initial - have to account for inconsistent data entry.

=LEFT(A1,LEN(TRIM(A1))-2)
This comment was minimized by the moderator on the site
Hi there,
im looking for a solution. Problem is that i have two colums of data where 1st column contain some text that part of is invalid and need to be removed, 2nd column contain invalid text string.

Let's say cell A1 contain text string "ABCDEF"

Cell A2 contain invalid part of the string, for example "CD"

After removing invalid text string, cell A1 will look like this: "ABEF".

How to do that? Thanks in advance!
This comment was minimized by the moderator on the site
Hey you can do this with a combination of functions:

=CONCATENATE(LEFT(Y4;FIND("c";Y4)-1);RIGHT(Y4;LEN(Y4)-FIND("c";Y4)-1))

Concatenate brings the two strings together, because you want to take a left side of the string and combine it with a right side of the string eliminating the middle values.
This comment was minimized by the moderator on the site
remove all but the characters after a certain character. =RIGHT(G2,LEN(G2)-FIND("[",G2,4)). I was doing this for states so I had to convert the state into a unique character to reduce it down to 1 character rather then " NM ".
This comment was minimized by the moderator on the site
Good day

Kindly assist in removing the letters which appear like that on numbers using excel

26772309814Trudie

Regards
This comment was minimized by the moderator on the site
Use the formula =RIGHT(A2,LEN(A2)-11)
This comment was minimized by the moderator on the site
Hai Ex : AS SPLSH (CW) 100ml (112) hove to remove last (sum numbers)
This comment was minimized by the moderator on the site
Worked for me. Thanks
This comment was minimized by the moderator on the site
very nice tip
This comment was minimized by the moderator on the site
Please tell me how to get particular data in a cell Example Column A Ip-bs-bedroom Gh-ff-kitchen Ip-gh-main door . . Now I want in column B has only location ,that's bedroom, kitchen so I want to remove other except location
This comment was minimized by the moderator on the site
You can use Find and Replace (Ctr + H). Find what: "*-" and Replace with: keep this field blank. Then click replace.
This comment was minimized by the moderator on the site
Dear Member, How do we find Data Required 201 205 2102 2105 203 205 2015 2015 207 210 2147 2150 1000 1000
This comment was minimized by the moderator on the site
Hi I want to remove everything after the first dash(-) and keep only the letters. How do I do that? example 123-ABC-BDER-AKD
This comment was minimized by the moderator on the site
try =RIGHT(A1, LEN(A1)-4) assuming characters before the first (-) are 3 digits long
This comment was minimized by the moderator on the site
Hi, I need support from Kutools team as i believe only you guys can help me. I have purchased a kutools license and couldn't find this feature in it. I would like to password protect/unprotect a 'sheet1' of 300 workbooks in one folder. For example: Only certain cells are editable in the password protected sheet1. You need to unprotect it with a password to edit all the cells. Scenario : When I open a workbook sheet1 opens and only certain cells are editable.(This is for on fields workers) For office purpose all the cells have to be editable and this is protected with a password which on field workers don't know. After some editing we have to protect back the sheet and save it. This happens for 300 workbooks' sheet1. So everytime Opening workbook -> Review->Unprotect Sheet->Enter Password. After Changes Review->Protect sheet->Enter Password->Save Can we do the above steps for a folder of workbooks using a VBA or any such help would be much appreciated.
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
Hi! I Would like to removed selected characters on my excel workbook, how will i do that? is there any formulas for it? your immediate advice is highly appreciated. Thanks & BR,
This comment was minimized by the moderator on the site
Hi. I would like to remove the last characters by using find and replace, but with Excel's Visual Basic (with commands). I would like to create a file that loads an existing file and creates a new one, with the column edited. Any help will be appreciated. Regards,
This comment was minimized by the moderator on the site
how can I mass delete a * in a excel spreadsheet description line?
This comment was minimized by the moderator on the site
Hi Yoda, just do a find and replace. You can easily acces the function using CTRL+F. You will want to look for "*" and replace with "" basically, wherever excel finds * it will replace it with a blank.
This comment was minimized by the moderator on the site
Thanks you for a very simple and a comprehensible approach. It works!!!! God bless you my dear Fellow-Anonymous! It took me 2 days of painful searches through Russian web and quite a number brain cells to find Excel Functions in Russian with explanatory notes provided, but all was in vain just because formulas refuse to work!! It seems like the accessibility and the total unwillingless to share common practices among my Russian colleagues, who just pretend they know *uck all about Microsoft products is still a big issue!! No, seriously, I owe u! Thanks!
This comment was minimized by the moderator on the site
send the kutools for xp
This comment was minimized by the moderator on the site
hey tanx dears Replace All worked for me. hhhh very good trick..
This comment was minimized by the moderator on the site
I want to delete the left 4 digit in whole sheet... for example 5895012032251........the result should be 012032251 I can do the one only but how to do in whole worksheet..
This comment was minimized by the moderator on the site
Hi Team, Need formula for removing first character from cell if it is special only. Kindly help...
This comment was minimized by the moderator on the site
use subsitute function
This comment was minimized by the moderator on the site
Hi all Is there any way to remove Vowels from a alpha string. say 'Connector, the result supposed to be 'cnnctor'.
This comment was minimized by the moderator on the site
Hi, The easiest way is to do a Find and replace. Select you range, do ctrl-F then find O replace with blank. This will remove all,'O's and replace with no character, which will remove them. Do this with each vowel and you are done. No formula required
This comment was minimized by the moderator on the site
Hi! Thank you so much for this article! It has saved me so much time.
This comment was minimized by the moderator on the site
My string is like this "1400 10-07-1975" I want to keep only the first "1400" I tried, It worked for the first time with that search and replace function... but I'm unable to repeat it by putting "space*" in "find what" section Please help...
This comment was minimized by the moderator on the site
=LEFT(A1,SEARCH(" ",A1)-1) Should do the trick. Basically saying; I want text starting from the left of the string in A1, then i want to find the location of the first instance of a space and minus one from that. (otherwise your found string will contain the space). Simple enough, hope this helps.
This comment was minimized by the moderator on the site
THANK GOD!!!! You just saved me two day's of work from Excel loosing all formatting. I'm going to champion this software for use within my group. You just got at least one new customer.
This comment was minimized by the moderator on the site
I used this one RIGHT(B1, LEN(B1)-2) Thanks
This comment was minimized by the moderator on the site
I have data that contains both Chinese and English and I would like to trim the data so that only the English appears. The data is varying lengths and the Chinese characters I would liked trimmed are also varying lengths. How might I be able able to do this. The data looks like this: 待摊车辆保险Prepaid vehicle insurance fee 待摊保险费Prepaid insurance fee 原材料Raw material
This comment was minimized by the moderator on the site
Hi, I need help with product database. I have names of products from database for example: /cars/new/car1.jpg /cars/car red2.jpg /trucks/new/for sale/34533.jpg So I need only the text after the last slash (jpg file name): car1.jpg car red2.jpg 34533.jpg I can't predict how long it is part before slash / and after slash. It is different for different cells. Any solution? Thank you so much
This comment was minimized by the moderator on the site
use column to text in data section
This comment was minimized by the moderator on the site
ABC xyz pq Replace Me(s) output: "ABC RE" EFG xyz pq Change This( output: "EFG CT" I have data similar to dummy data in above 2 lines in excel. Request your help to provide a formula that will replace the complete line with "RM" if found with text "Replace Me(s)" and "CT" if found with text "Change This("
This comment was minimized by the moderator on the site
25/06/1994 27/12/1984 01/07/1991 12/02/1977 12/02/1977 09/07/1976 01/01/1974 01/01/1974 hello, from the above, how do i remove the first characters, i want to remain with the last 4 digits eg 1977, 1976,
This comment was minimized by the moderator on the site
=year(date)
you can get year
This comment was minimized by the moderator on the site
[quote]25/06/1994 27/12/1984 01/07/1991 12/02/1977 12/02/1977 09/07/1976 01/01/1974 01/01/1974 hello, from the above, how do i remove the first characters, i want to remain with the last 4 digits eg 1977, 1976,By SAMULA[/quote] Try =RIGHT(A1,4) This says; Starting from the right of cell A1, give me the last 4 characters.
There are no comments posted here yet