How to ensure cell content follows defined formatting rules

[hazz1234567]

Hi guys,

Need a bit of help here. I am trying to put in some controls in an Excel sheet to ensure data entered follow a particular formatting style. I am hoping to do this via either data validation or some sort of conditional formatting to show conformance. The content of the cell needs to follow this format ex. AA BBB CCC DD-200-EEE-FFF-GGG mostly text with spaces and hypen with a 3 digit number somewhere in the middle.

I would appreciate it if someone can help advise how best to achieve this. Thanks.

 

[bobsan42]

I personally would design a UserForm with separate text boxes for each section of the reference number.
Then test each box for conformity and combine them properly after all is fine and save the value in the cell.
A lot of automation can be done to facilitate easier user input.