Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
or

Register

or
0
0
0
s2smodern

## How to remove first, last or certain characters from text in Excel?

We may use Excel functions to remove characters from a text string in Excel. This article is going to talk about the solutions to remove certain characters from a text string.

Remove first or last characters by formulas in Excel

Remove first or last characters by User Defined Function in Excel

 Remove first, last or certain characters from a list of text strings: To delete first or last n characters from a list of text strings, Kutools for Excel's Remove by Position utility can help you to solve this task as quickly as possible in Excel worksheet. Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

#### Remove first or last characters by formula in Excel

###### Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

With the Excel LEFT and RIGHT function, you can remove the certain characters from the beginning or the end of the strings. Please do as the following steps:

Remove the first four characters from the text string:

Type the following formula in a blank cell C2 where you want to put the result:=RIGHT(A2, LEN(A2)-4), and then drag the fill handle down to the cells that you want to fill this formula, and the first 4 charatcers have been removed from the text strings, see screenshot:

Tips: This formula means to return the right most number of characters, you need to subtract 4 characters from left string. And you can specify the number of characters you want to remove from the left string by changing the Number 4 in the formula =RIGHT(A2, LEN(A2)-4).

Remove the last six characters from the text string:

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

Please enter this formula:=LEFT(A2, LEN(A2)-6) into a blank cell, and then drag the fill handle down to the cells that you want to apply this formula, and last 6 charctaers have been deleted from the text strings at once, see screenshot:

Note: Using the Excel function to remove certain characters is not as directly as it is. Just take a look at the way provided in next method, which is no more than two or three mouse clicks.

#### Remove first or last characters by User Defined Function in Excel

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

Remove the first four characters from the text string:

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(A2,4) into a blank cell, and then drag the fill handle down to get the results as you need, see screenshot:

Notes:

1. In the above formula, A2 is the cell that you want to remove charaters, and the number 4 indicates the number of charaters you would like to remove.

2. To remove last n charaters 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(A2,6) to get the rsults as you need, see screenshot:

#### Remove first, last, or certain characters with Kutools for Excel

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 in one click.

 : with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

After installing Kutools for Excel, 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 charaters, see screenshot:

Tips:

1. For removing the last n characters, please apply the following operations in the dialog box, see screenshot:

2. For removing the certain characters at specified position, please apply the following operations in the dialog box, see screenshot:

Note: To avoid non-text cell, please choose Skip non-text cell.

#### Remove first, last, or certain characters with Kutools for Excel

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!

### Recommended Productivity Tools

#### Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

#### Kutools for Excel

Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

200 New Features for Excel, Make Excel Much Easy and Powerful:

• Merge Cell/Rows/Columns without Losing Data.
• Combine and Consolidate Multiple Sheets and Workbooks.
• Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
• Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
• More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Say something here...
symbols left.
###### or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
• To post as a guest, your comment is unpublished.
· 7 months ago
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?
• To post as a guest, your comment is unpublished.
· 8 months ago
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?
• To post as a guest, your comment is unpublished.
· 10 months ago
Helpful, thanks
• To post as a guest, your comment is unpublished.
· 11 months ago
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?
• To post as a guest, your comment is unpublished.
· 8 months ago
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
• To post as a guest, your comment is unpublished.
· 8 months ago
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
• To post as a guest, your comment is unpublished.
· 9 months ago
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)
• To post as a guest, your comment is unpublished.
· 8 months ago
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)
• To post as a guest, your comment is unpublished.
· 11 months ago
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!
• To post as a guest, your comment is unpublished.
· 11 months ago
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.