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, 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. Download and free trial Now!
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.
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:
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:
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:
2. Specify the following operations in the pop-up Remove by Position dialog box.
- Specify the number of characters to be deleted.
- Choose From left option under the Position section to delete the first n charaters, see screenshot:
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.
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
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
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...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 21 days agoHow 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.· 28 days agoThis 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.· 2 months agoThis really helps me a lot in my daily working.
To post as a guest, your comment is unpublished.· 2 months agoHi,
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.· 3 months agoHow can I type the command to cut text from the left and the right?
- ← Previous
- Next →