Column Validation Help

Question

Hi,

I'm trying to add some validation to a column so that the column has to follow the pattern "DUM-000000" or "MDM-000000" but I can't get it to work and I don't know why (why Microsoft don't just use RegEx I'll never understand).

The function I'm trying is:

=AND(OR(LEFT([LocalAssetRef]="MDM", 3), LEFT([LocalAssetRef]="DUM", 3)), LEN([LocalAssetRef]) = 10, MID([LocalAssetRef], 4,1) = "-", ISNUMBER(MID([LocalAssetRef], 5, 6)))

Thing's I've already tried but haven't worked (above is the original function):

• I thought maybe it was an issue with the ISNUMBER reading it as a string so I removed that
• I thought maybe the AND was outdated and would only allow 2 items, so I corrected for that

Any help would be appreciated, or if anyone knows of an online tool which would help then that would also be of use. SharePoint unhelpfully returns 'this hasn't worked' instead of telling me where the failure is.

Answer 1

Dear respected Joe Curry,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

I’m sorry you’re having a problem but based on your description we can work together to resolve it.

For the situation you encountered, we deeply understand the inconvenience caused and apologize for it.

As per your description, here is other way: I tried below formula in validation to a column "LocalAssetRef".

=AND(OR(LEFT(LocalAssetRef,3)="MDM",LEFT(LocalAssetRef,3)="DUM"),LEN(LocalAssetRef)=10,MID(LocalAssetRef,4,1)="-",ISNUMBER(VALUE(MID(LocalAssetRef,5,6))))

If there is any misunderstanding, I apologize and please feel free to post back to us with more detailed information and show us an expected result for better understanding and guiding you further.

Please understand that our initial reply may not always immediately resolve the issue. However, with your help and more detailed information, we can work together to find a solution.

I'm sorry and I apologize for that.

Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely,

Waqas Muhammad

Answer 2

Does SharePoint work like Excel? If so, try

=AND(OR(LEFT([LocalAssetRef], 3)="MDM", LEFT([LocalAssetRef], 3)="DUM"), LEN([LocalAssetRef])=10, MID([LocalAssetRef], 4, 1)="-", ISNUMBER(--RIGHT([LocalAssetRef], 6)))

Answer 3

Hi Joe Curry,

Thanks for your valuable feedback and sharing updates with us and I really appreciate your effort and your precious time doing. 

And I appreciate your kind words.

It's good to hear that you found an alternative using Power Apps and the IsMatch function! SharePoint list validation can be quite limited, especially when integrating with Power Apps.

Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Sincerely,

Waqas Muhammad

Answer 4

Had 2 brill answers which solved my initial issue. Just in case anyone else finds themselves in the same position as me, I eventually found that SharePoint list validation doesn't pull through to Power Apps forms so had to use IsMatch in my forms instead (IsMatch accepts RegEx so in the end it turned out to be a better solution anyway, except you have to set it multiple times as you can't set a constant in Power Apps..)

I found the answer to my Power Apps problem here: https://wonderlaura.com/2019/07/23/powerapps-text-input-pattern-validation/