Skip to main content

How to remove prefix / suffix from multiple cells in Excel?

doc remove prefix 1

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


arrow blue right bubble 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:

doc remove prefix 2

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:

doc remove prefix 3

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:

doc remove prefix 4

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.


arrow blue right bubble 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:

doc remove prefix 6

(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:

doc remove prefix 7

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!


arrow blue right bubble Remove prefix or suffix from multiple cells with Kutools for Excel

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

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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

Description


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!
Comments (11)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
DELETE - 100 ML (100) I WANT TO ONLY 100 ML
This comment was minimized by the moderator on the site
Thanks. This is good.
This comment was minimized by the moderator on the site
Really helpful
Thank You
This comment was minimized by the moderator on the site
really helpful. thanks!!!
This comment was minimized by the moderator on the site
How do I get the value 1234 for a cell 0001 2314 2571 1234 9874? Please assist on this.
This comment was minimized by the moderator on the site
With Kutools it worked like a charm! Big thanks!
This comment was minimized by the moderator on the site
Thank you so much! Very helpful!!!!!!
This comment was minimized by the moderator on the site
Very clearly explained. Thank you!
This comment was minimized by the moderator on the site
This was very helpful, thank you!
This comment was minimized by the moderator on the site
Worked very well, thanks
This comment was minimized by the moderator on the site
Very clearly explained. Thank you!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations