How to Remove a Prefix and Sort Part Numbers in Microsoft Access
Question details
The user needs to remove an 8-character prefix from a part number field in Microsoft Access and sort the remaining characters numerically.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating a query to extract and sort the numerical sequence of part numbers after discarding a fixed-length text prefix.
- Observed behavior
- Standard text subtraction does not work; the user needs a functional method to isolate the suffix and treat it as a number for accurate sorting.
Ensure you know the exact character length of the prefix you want to remove, and verify that the remaining characters in your part number field are purely numeric.
Use a Calculated Query Field with Mid and Val Functions
Extract the numeric portion of the part number using the Mid function, and convert it to a true number using the Val function for accurate sorting.
Subtracting one text string from another is not supported in Access. Instead, text manipulation functions must be used to extract the desired characters from a string.
Launch Microsoft Access, locate the specific query where you want to sort the part numbers, right-click it, and select 'Design View'.
In an empty column within the query design grid, enter the expression: LastPart: Val(Mid([PRT#],9)) (replace [PRT#] with your actual part number field name).
In the 'Sort' row of your newly created calculated field column, click the drop-down menu and select 'Descending' to identify the highest number, or 'Ascending' for normal sequential order.
Click the 'Run' button (the red exclamation point icon) on the Query Design ribbon to execute the query and view your properly sorted sequence.

Permanently Split the Field in Table Design
If removing the prefix is a frequent requirement across multiple queries, it is often more efficient to restructure your table to store the prefix and the sequence number in separate fields.
Manage and Sort Tabular Data Easily with WPS Office
While Microsoft Access is built for complex relational databases, many data extraction and sorting tasks can be performed much faster in spreadsheet software. WPS Office is a free, lightweight, and highly compatible alternative to Microsoft Office, offering powerful spreadsheet functions to manage and sort your data without writing complex database queries.
- 1. Download and Install: Download WPS Office Free and install the suite on your computer.
- 2. Import Your Data: Launch WPS Spreadsheet and open your exported Access data or existing Excel workbook.
- 3. Use Extraction Functions: Use the formula =VALUE(MID(A2, 9, 100)) in a new column to extract and convert the sequence numbers, then click the Sort feature on the Data tab.

Frequently Asked Questions
Why does Access sort my sequence numbers like 1, 10, 2 instead of 1, 2, 10?
This happens when the field is stored as Text rather than a Number. Text fields are sorted alphabetically, character-by-character. Using the Val() function converts the text string into a numeric value, enabling proper sequential sorting.
Can I use the Right() function instead of Mid() to remove a prefix?
Yes, but only if the suffix sequence is always a fixed length. For example, Right([PRT#], 4) will always extract the last 4 characters. If the suffix length varies (e.g., some are 3 digits, others are 5 digits) but the prefix is a constant 8 characters, Mid([PRT#], 9) is the most accurate approach.
How do I deal with spaces after removing the prefix?
If your extraction leaves leading or trailing spaces, you can wrap the text extraction function in the Trim() function, such as Trim(Mid([PRT#], 9)), before applying Val() to convert it into a number.
Does creating a calculated query field alter my original table data?
No. A calculated field in a Select Query only manipulates and displays the data dynamically for viewing or reporting purposes. Your original part number field in the source table remains completely unchanged.




