Others

How to Remove a Prefix and Sort Part Numbers in Microsoft Access

Algirdas JasaitisAlgirdas Jasaitis Oct 13, 2026 868 views

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.

How to Remove a Prefix and Sort Part Numbers in Microsoft Access
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the query in Design View

Launch Microsoft Access, locate the specific query where you want to sort the part numbers, right-click it, and select 'Design View'.

2
Create the calculated field

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

3
Apply the sort order

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.

4
Run the query

Click the 'Run' button (the red exclamation point icon) on the Query Design ribbon to execute the query and view your properly sorted sequence.

Use a Calculated Query Field with Mid and Val Functions
Function Breakdown: The Mid function starts reading from the 9th character, effectively ignoring the 8-character prefix. The Val function then converts those remaining text characters into numeric values so they sort sequentially (e.g., 1, 2, 10) rather than alphabetically (e.g., 1, 10, 2).
Free Microsoft Office alternative

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. 1. Download and Install: Download WPS Office Free and install the suite on your computer.
  2. 2. Import Your Data: Launch WPS Spreadsheet and open your exported Access data or existing Excel workbook.
  3. 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.
Highly compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Familiar user interface makes data migration and daily use seamless.Free and lightweight, requiring minimal system resources.Built-in text functions (MID, RIGHT, LEFT, VALUE) for easy data manipulation.
microsoft office alternative - wps office

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.