Specific Validation (RegEx?) without VBA?

[MartinS]

I am trying to find out if I can achieve something in Excel to apply some validation, but without using VBA if possible?
I have a requirement to validate a column to allow either NNNNNNN.NN or NNNNNNN.NN.NN (N always being numeric). The data will then be imported into SQL by another process, but the requirement is for the user to not enter 'rubbish' before the import process starts, but they also want the worksheet that contains the data to be copied/pasted to other workbooks, so having VBA behind it would not work for sheet once it was in another workbook. If VBA is the only option then we will consider an add-in but before we go down that road, I wanted to check to see if there was something I'm missing with data validation?
Thanks in advance
Martin

 

[RoryA]

I haven't tested extensively, but a custom validation formula something like this should work:

=AND(OR(LEN(A1)=10,LEN(A1)=13),ISNUMBER(--LEFT(A1,7)),ISNUMBER(--RIGHT(A1,2)),MID(A1,8,1)=".",IF(LEN(A1)=13,ISNUMBER(--MID(A1,9,2)),TRUE))

where A1 is the cell you are validating.

 

[Scott Huish]

Slightly different:

=AND(ISNUMBER(--SUBSTITUTE(A1,".","")),OR(LEFT(RIGHT(A1,3))=".",AND(LEFT(RIGHT(A1,6))=".",LEFT(RIGHT(A1,3))=".")),OR(LEN(A1)=10,LEN(A1)=13))

 

[MartinS]

Thanks guys, really helpful. I shall give these a try but they both look to be doing exactly what I'm looking for.
Thanks again
Martin