Very Customized Data Validation

[Ricardo]

Hi Chandoo,


Maybe you could help me.


I need to do a very customized Data Validation. The parameters:


1. It is a number.

2. It has leading Zeros, can be serveral

3. It has 10 digits and I want to show an error message like "Please fill with exactly 10 digits" if the cell is filled with more or less than 10 digits.

4. It must have the following format: XXXXX.XXXXXX (two groups of five digits separated by a dot)


There is no Validation option that embrace all this particulrities on Excel 2007. I tried the number of lenght equals 10 but it does not count the leading Zeros.


Thanks

 

[keymaster]

Ricardo...


Welcome to Chandoo.org forums and thanks for your question.


You can use Custom data validation to do this. Follow below steps.


1. select the cell(s) which need to have this validation.

2. go to data validation and select custom as rule type

3. type =AND(ISNUMBER(B2+0),LEN(B2)=11,FIND(".",B2)=6)

replace B2 with actual top-left cell address with relative reference.

4. Go to error Error alert tab and type this

title= Invalid data

message = Please enter only

-numbers

-must be in format xxxxx.xxxxx

-add leading zeros if necessary

5. click ok.

6. Format all the cells for which you want this validation as TEXT (from home > format)


This will do what you want.

 

[Ricardo]

Hi Chandoo, good morning


Two things:


1. I can only place the formula if take the "=" symbol out, this way: AND(ISNUMBER(B2+0),LEN(B2)=11,FIND(".",B2)=6)


2. I did not understand what means "replace B2 with actual top-left cell address with relative reference". The cell I want to do the validation is A3. Replacing B2 by A3 I received the Error Alert everytime, even when I use 10 digitis. Which address should I use then or is there any mistake on the formula?


Thanks and expecting your comments

 

[SirJB7]

Hi, Ricardo!

Consider uploading a sample file so as to get it updated from anyone who read this and might be able to help you. Thank you.

Give a look at the second green sticky post at this forums main page for uploading guidelines.

Regards!

 

[keymaster]

@Ricardo... you need to format A3 as text. Also use this formula.

AND(ISNUMBER(A3+0),LEN(A3)=11,FIND(".",A3)=6)


See this example file:

http://img.chandoo.org/playground/complex-data-validation.xlsx

 

[Ricardo]

Hi Chandoo,


I got your point now. The way you did, it is needed to type the dot (.) and I was trying with out typing it, that's why it was not working.


Could we do in a way that the dot (.) does not need to be typed but shows with it afterwards?


Regards

 

[keymaster]

@Ricardo...


With this setup, there is no automatic way to add decimal points using Excel alone. You can do this with VBA. But for such a small thing, writing VBA may not be the right way to go.


My suggestion is like this:

1. When entering data, let users type just 10 digit numbers (with leading zeros if needed).

2. When displaying, you take the value from data entry cell and convert it to the format you want using a formula like this =left(A3,5)&"."&right(A3,5)

 

[Ricardo]

Hi Chandoo,


I do not understand. Do I need to do a VBA code as you suggested or it is something I can do using the Excel alone?


If it is to use the Excel alone, how I do that? How do I separate the typing from the displayng?


Regards,

 

[Ricardo]

Chandoo,


I figured out some issues. The Excel I`m using is in Portuguese, strangely some functions work if I state them in English, some not, I need to state in Portuguese.


Secondly, it uses ";" instead of "," as argument separator.


Stating that, could you explaing if there is an way to separate the entering operation from the displaying?