Cleaning data in Excel is a key step. It gets the data ready for analysis, reporting, or importing into other systems. A common issue for professionals is prefixes in data entries. Codes often start with “SKU-”, “INV_”, or “ID#”. These prefixes can hinder sorting, filtering, or data matching. This guide shows you how to remove prefixes in Excel. You’ll find different methods here, from simple formulas to advanced functions.
What is a Prefix?
A prefix is a set of characters added before the main content of a text or number string. In Excel datasets, prefixes are often added for identification, categorization, or formatting.
Examples of Prefixes:
- “SKU-12345” → Prefix = “SKU-”
- “ID#4567” → Prefix = “ID#”
- “INV_2025-001” → Prefix = “INV_”
Prefixes help with categorization, but they can cause issues. This is especially true when you need to pull out core data, such as numerical IDs or codes.
How to Remove Prefix in Excel?
You can remove prefixes in Excel in a few ways. It depends on if the prefix has a fixed length or a variable one.
Method 1: Using the RIGHT and LEN Functions (Fixed Prefix)
If the prefix is always the same (e.g., “SKU-“), use:
=RIGHT(A2, LEN(A2) – 4)
This removes the first 4 characters.
Method 2: Using REPLACE Function
To remove the first n characters:
=REPLACE(A2, 1, 4, “”)
Removes 4 characters from the start of the string.
Method 3: Using MID Function
This formula also works for fixed-length prefixes:
=MID(A2, 5, LEN(A2))
It starts from the 5th character and gets the rest.
Method 4: Using TEXTAFTER Function (Dynamic Prefix – Excel 365)
For newer Excel versions, if the prefix ends with a known character (e.g., a dash or underscore):
=TEXTAFTER(A2, “-“)
This returns the text after the first dash (“-”).
- Select your data → Go to Data > From Table/Range.
- In Power Query Editor, use Transform > Extract > Text After Delimiter.
- Enter the delimiter (e.g., “-” or “_”).
- Click Close & Load to return cleaned data to Excel.
Examples
| Original Data | Formula Used | Result |
| SKU-4567 | =TEXTAFTER(A2, “-“) | 4567 |
| ID#8901 | =REPLACE(A2, 1, 3, “”) | 8901 |
| INV_2025-001 | Power Query (delimiter “_”) | 2025-001 |
| REF-XY123 | =RIGHT(A2, LEN(A2) – 4) | XY123 |
Benefits of Removing Prefixes in Excel
Clean and Consistent Data
Removing prefixes gives uniform values. This makes it easier to analyze, compare, and visualize data. There are no inconsistencies from formatting differences.
Improved Data Matching and VLOOKUPs
Functions like VLOOKUP, XLOOKUP, and INDEX-MATCH require exact matches. Prefixes can break these functions. Removing them ensures seamless lookups.
Better Importing into Databases or Systems
Many third-party tools, APIs, and CRMs reject data that has special characters or unusual prefixes. Cleaning the prefix ensures smooth integrations and uploads.
Time-Saving for Large Datasets
Manually editing thousands of rows to remove prefixes is time-consuming. With Excel formulas or Power Query, the process becomes instant and scalable.
Professional and Polished Reports
Final reports and dashboards should present readable, concise data. Removing prefixes declutters values, making reports more client-friendly and professional.
FAQ’s on Removing Prefixes in Excel
Can I remove different prefixes from each row?
Yes, but you’ll need to use SEARCH, FIND, or TEXTAFTER with dynamic logic. This is important if prefixes change in length or content.
What if the prefix is numeric?
Excel treats text and numbers the same in string operations. All the above methods still work unless the cell is formatted as a number.
Can I use Find & Replace for this?
Yes. If the prefix is the same (like “SKU-”), press Ctrl + H. Type the prefix in Find What. Leave Replace With empty. Then, click Replace All.
Will Power Query remove prefixes from multiple columns?
Yes. You can transform multiple columns with custom or built-in Power Query functions.
Remove Prefix and Suffix in Given Cells in Excel
Conclusion
Removing prefixes in Excel is an important data cleaning step. It helps you sort, filter, analyze, and present your information better. Removing prefixes from SKU codes, ID numbers, or imported system data is easy. You can use formulas or Power Query. This skill saves time, cuts errors, and boosts your data quality. Excel’s toolkit is always changing. Now, even complex data tasks are easier, thanks to new functions like TEXTAFTER. Use these techniques to ensure your data remains clean, standardized, and analysis-ready.