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

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 with Kutools for 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.

delete first last n characters 9

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Remove first or last characters by formula in Excel


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:

delete first last n characters 1

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:

delete first last n characters 2

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:

delete first last n characters 3

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:

delete first last n characters 4


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.

Kutools for Excel : with more than 120 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:

delete first last n characters 5

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:

delete first last n characters 6

Tips:

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

delete first last n characters 7

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

delete first last n characters 8

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

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

Kutools for Excel

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

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

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Sameer · 21 days ago
    How to remove the first character from a cell but not in every cell in that column? Thanks!
  • To post as a guest, your comment is unpublished.
    Randy Ralston · 28 days ago
    This helped to get the result from an entry but I am unable to LOOKUP the new value.
  • To post as a guest, your comment is unpublished.
    Mohammad Barzakh · 2 months ago
    This really helps me a lot in my daily working.

    Thanks
  • To post as a guest, your comment is unpublished.
    siena · 2 months ago
    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.

    Thank you in advance.
  • To post as a guest, your comment is unpublished.
    Jesus · 3 months ago
    How can I type the command to cut text from the left and the right?