How to Remove a 5-Digit Prefix from Excel Values
Question details
The user needs to remove a specific 5-digit prefix from a column of data values in Excel.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning or formatting a dataset where cell values contain an unwanted 5-digit prefix.
- Observed behavior
- Extracting all characters starting from the sixth character to yield the desired values without the prefix.
Ensure you have a blank column next to your data where you can insert the extraction formulas without overwriting adjacent information.
Use the MID Formula to Extract Text
The MID function is the quickest way to strip a fixed number of starting characters from your values using a simple formula.
This method uses the MID function to extract characters starting from the 6th position. The double negative (--) is used to convert the extracted text back into a numerical value.
Click on the first cell in an empty adjacent column (e.g., C2 if your data starts in B2).
Type the formula =--MID(B2,6,100) into the cell and press Enter. The number 100 acts as an arbitrary large number to ensure the entire remainder of the string is captured.
Click and drag the fill handle (the small square at the bottom-right corner of the cell) down to apply this formula to the rest of the column.
To replace the original values, select your new formula column, press Ctrl+C to copy, right-click the original data column, and select 'Paste as Values'.
Use Power Query for Reusable Data Cleaning
Power Query is highly effective if you are regularly updating or refreshing data and want to permanently remove the source column automatically.
Easily Clean and Process Data with WPS Spreadsheet
WPS Spreadsheet provides all the advanced formulas, including MID, LEFT, and RIGHT, to help you manipulate and clean your dataset efficiently.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data with the prefixes.
- 2. Insert the MID formula: In an empty adjacent column, type =--MID(B2,6,100) to strip the first 5 digits.
- 3. Apply to the entire column: Double-click the fill handle to copy the formula downwards for all rows.
- 4. Paste as values: Copy the newly generated column and use 'Paste Special' > 'Values' over your original data to finalize the removal.
Frequently Asked Questions
How do I remove a variable length prefix instead of exactly 5 digits?
If the prefix length varies but is separated by a specific character (like a hyphen or space), you can use a combination of the MID and SEARCH functions, or use the 'Text to Columns' feature under the Data tab using that specific character as a delimiter.
Why does my formula result show a #VALUE! error?
This error usually occurs if the original cell contains text or special characters that cannot be mathematically evaluated when using the double negative (--). Try removing the -- from the formula if you only need a text extraction.
Can I use the RIGHT function instead of MID to remove a prefix?
Yes, but only if the total length of your text is constant. For example, if every cell has exactly 15 characters and you want to drop the first 5, you can use the formula =RIGHT(B2, 10).
