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 prefix or suffix from multiple cells with formulas
Remove prefix or suffix from multiple cells with Kutools for Excel
Remove prefix or suffix from multiple cells with formulas
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:
Notes:
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.
Remove prefix or suffix from multiple cells with Kutools for Excel
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.
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.
Click to know more about this Remove by Position feature.
Click to Download Kutools for Excel and free trial Now!
Remove prefix or suffix from multiple cells with Kutools for Excel
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (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! (Full-Featured 30-Day Free Trial)
