Custom number format with text

**TechRetard** · 01-14-2011, 08:12 PM

newb formula of the day

say you are inputing into cell b1...use data validation, custom formula

=AND(LEN(B1)=10,CODE(LEFT(B1))>=65,CODE(LEFT(B1,1))>=65,CODE(LEFT(B1,2))>=65,CODE(MID(B1,4,1))=45,CODE(LEFT(B1,4))<=90,CODE(LEFT(B1,5))<=90,CODE(LEFT(B1,6))<=90,CODE(LEFT(B1,7))<=90,CODE(LEFT(B1,8))<=90,CODE(LEFT(B1,9))<=90,CODE(LEFT(B1,10))<=90)

drag down

**mahju** · 01-18-2011, 06:26 AM

A modification to the above formula

F8:
=AND(ISNUMBER(VALUE(RIGHT(F8,6))),LEN(F8)=10,CODE(MID(F8,4,1))=45,CODE(LEFT(F8,1))>=65,CODE(LEFT(F8,1))<=90,CODE(MID(F8,2,1))>=65,CODE(MID(F8,2,1))<=90,CODE(MID(F8,3,1))>=65,CODE(MID(F8,3,1))<=90,CODE(MID(F8,9,1))<>69)

Copy + Past special to copy

Regards

**karstens** · 01-18-2011, 10:40 AM

I tried the suggestion in the B1 cell, and also the suggestion in the F8 cell. I copied the formula into the custom validation window, but both formulas bring up a dialog box that says this will result in an error.
I am not sure what I am doing wrong.
Thank you for the suggestions.