SharePoint Document Issues

How to Validate DUM-000000 or MDM-000000 Format in SharePoint Columns

Tauseeq Magsi Sep 27, 2026 869 views

Question details

The user needs a SharePoint column validation formula to strictly enforce a specific text format: either 'DUM-' or 'MDM-' followed by exactly six digits.

How to Validate DUM-000000 or MDM-000000 Format in SharePoint Columns

Product: Microsoft SharePoint
Device & OS: not provided
Scenario: Configuring custom column validation rules in a SharePoint list or library to maintain data consistency.
Observed behavior: A requirement to strictly validate text strings based on a specific prefix, hyphen placement, total length, and numeric suffix.

Before you start

Ensure you have Full Control or Design permissions on the SharePoint list to modify column settings and apply validation formulas.

Solution 1Recommended

Apply Native SharePoint Column Validation Formula

Use a combination of logical and text functions to validate the exact string format directly in the SharePoint list settings.

SharePoint allows you to enforce strict data entry rules using Excel-like formulas. The formula checks if the first three characters match the required prefixes, ensures the total length is exactly 10 characters, verifies the hyphen is at the 4th position, and confirms the last 6 characters are numbers.

Navigate to List Settings

Open your SharePoint list, click the Gear icon in the top right corner, and select 'List settings'.

Select the Target Column

Scroll down to the Columns section and click on the column you want to validate (e.g., LocalAssetRef).

Enter the Validation Formula

Expand the 'Column Validation' section and paste the following formula: =AND(OR(LEFT([LocalAssetRef],3)="MDM",LEFT([LocalAssetRef],3)="DUM"),LEN([LocalAssetRef])=10,MID([LocalAssetRef],4,1)="-",ISNUMBER(VALUE(MID([LocalAssetRef],5,6))))

Save the Configuration

Add a user message to explain the requirement if validation fails, then click 'OK' to save your changes.

Apply Native SharePoint Column Validation Formula

Bracket Usage: If your column name contains spaces or special characters, you must wrap the exact column name in brackets, such as [Asset Reference].

Solution 2

Use Power Apps IsMatch for Customized Forms

Since SharePoint column validation may not carry over to Power Apps forms, use the IsMatch function with a regular expression when customizing the list form.

Free Microsoft Office alternative

Manage Complex Data Efficiently with WPS Office

While SharePoint handles list-based data, preparing and validating large datasets before uploading is much easier with a robust spreadsheet tool. WPS Office provides a lightweight, highly compatible alternative to Microsoft Office, complete with powerful data validation features.

1. Download WPS Office: Visit the official WPS website to download the free installation package for your operating system.
2. Open WPS Spreadsheet: Launch WPS Office and open a new Spreadsheet to begin managing your dataset.
3. Use Data Validation: Highlight your data column, navigate to the 'Data' tab, and click 'Data Validation' to enforce custom formula rules similar to SharePoint.

Fully compatible with Microsoft Excel (.xlsx), Word, and PowerPoint formats.Includes advanced Data Validation tools to restrict input lengths and specific text formats.Familiar user interface that ensures a seamless transition for Microsoft Office users.Lightweight installation and incredibly fast startup speeds.Free to use for everyday document, spreadsheet, and presentation management.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SharePoint validation formula returning a syntax error?

Syntax errors usually occur if column names are misspelled, not properly wrapped in brackets (e.g., [Column Name]), or if your regional SharePoint settings require semicolons instead of commas to separate formula arguments.

Does SharePoint list validation apply automatically to Power Apps?

No, SharePoint list column validation rules generally do not carry over to customized Power Apps forms. You must configure validation separately within Power Apps using the IsMatch function.

Can I validate multiple formats in one SharePoint column?

Yes. By wrapping multiple logical checks inside an OR function within your validation formula, you can configure a column to accept various distinct formats, such as allowing both 'DUM-' and 'MDM-' prefixes simultaneously.

Document & File Issues