How to remove prefix / suffix from multiple cells in Excel?
For example, I have the following list of text strings in a worksheet, and now, I want to remove the prefix KTE or suffix –No from the cells to get the below screenshot result. How could I remove the prefix or suffix from multiple cells in Excel at once?
Remove first, last or specified position characters from text strings:
Kutools for Excel’s Remove by Position utility can help you to delete the specific characters from beginning, middle or end of the text strings within multiple cells at once.
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!
To remove the same number prefix or suffix, you can apply the following simple formulas.
1. Removing the first three prefix characters from the text strings, please enter this formula: =RIGHT(A2,LEN(A2)-3), see screenshot:
Tips: A2 is the cell that you want to use and the number 3 indicates the leftmost three characters in the text you want to remove. This formula means to remove the leftmost three characters and return other characters of the text string. You can change them to your need.
2. Then press Enter key to return the result, and select the cell C2, drag the fill handle over to the cells that you want to apply this formula, and all the first three prefix characters have been removed from the text strings, see screenshot:
1. To remove the last three suffix characters from the multiple cells, please type this formula: =LEFT(A2,LEN(A2)-3), (it means remove the rightmost three characters from the string, you can change the variables A2 and 3 to your need), and then drag the fill handle down to the cells you need to contain this formula, you will get the following result:
2. As they are formulas, when you copy and paste them to other cells, make sure to paste them as values to avoid the formula errors.
If you have Kutools for Excel, with tis Remove by position feature, you can quickly remove any number characters from the left cell, right cell or certain position in the original range cells.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
After installing Kutools for Excel, please do as following:( Click to Download Kutools for Excel Now )
1. Select the data range that you want to remove the prefix or suffix.
2. Click Kutools > Text > Remove by Position, see screenshot:
3. In the Remove by Position dialog box:
(1.) To remove the first prefix characters, please specify the number of characters to be removed, and then choose From left under the Position section, see screenshot:
(2.) To remove the last suffix characters, please specify the number of characters to be removed, and then select From right under the Position section, see screenshot:
4. Then click Ok or Apply button, you can view the results from the Preview pane at the same time, and you will get your wanted results.
Note: Skip non-text cells: this option will avoid non-text cells from the selected range.
Excel Productivity 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.· 2 months agoThanks. This is good.
To post as a guest, your comment is unpublished.· 2 months agoReally helpful
To post as a guest, your comment is unpublished.· 1 years agoreally helpful. thanks!!!
To post as a guest, your comment is unpublished.· 1 years agoHow do I get the value 1234 for a cell 0001 2314 2571 1234 9874? Please assist on this.
To post as a guest, your comment is unpublished.· 1 years agoWith Kutools it worked like a charm! Big thanks!