How to remove prefix / suffix from multiple cells in Excel?
Sometimes, you may need to clean up data in Excel by removing prefixes or suffixes from multiple cells. Whether it’s for data consistency or formatting, removing these extra characters can help you standardize your dataset efficiently. In this guide, we'll explore two straightforward methods to achieve this task.
Remove prefix or suffix from multiple cells with formulas
If you prefer using Excel formulas to remove prefixes or suffixes, Excel provides versatile functions for this purpose. By utilizing functions such as LEFT, RIGHT, and LEN, you can extract the desired parts of your text data.
Remove prefix from cells
The formula provided in this section can be used to remove the same number of prefix,such as the first three characters from specified cells. Please do as follows.
Select a blanl cell, enter the following formula and press Enter. Select this result cell and drag its Fill Handle down to get the rest of the results.
=RIGHT(A2,LEN(A2)-3)
Remove suffix from cells
To remove suffix such as the last three characters from cells, try the following formula.
Select a blanl cell, enter the following formula and press Enter. Select this result cell and drag its Fill Handle down to get the rest of the results.
=LEFT(A2,LEN(A2)-3)
- A2 is the cell reference where the text you want to manipulate is located. You can change this reference to point to any cell that contains the text you need to edit.
- -3 represents the number of characters you will remove either from the beginning or the end of the text. If you need to remove a different number of characters, simply replace 3 with the desired number.
Easily remove prefix or suffix from cells with Kutools for Excel
For a quicker and more user-friendly approach, Kutools for Excel offers a powerful tool to remove prefixes and suffixes with just a few clicks. This method is ideal if you want to simplify the process without dealing with complex formulas.
After downloading and installing Kutools for Excel, click Kutools > Text > Remove by Position to open the Remove by Position dialog box. Then you need to configure as follows:
- Select the range of cells from which you want to remove the prefix or suffix.
- Input the number of characters to be removed in the Number text box.
- Specify whether you want to remove the prefix or suffix in the Position section Remove the prefix, select the From left optionRemove the suffix, select the From right option
- Click OK
Result
Then, the specified number of prefix or suffix characters will be removed from each cell in the selected range.
Remove prefix or suffix from multiple cells with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
