Can anybody help on an excel formula requirement.....?

[Thyagaraj]

Iam in requirement of a formula in excel for recognising text and
formula for data validation as below.

Validation required:-

1 - The cell should consist of only 10 characters
2 - The first 5 characters should be only text charecters ( eg:
a,b,c........).
3 - The next 4 characters should be only numeric characters ( eg:
1,2,3.....).
4 - The last 10th character should be a text chracter only.

Is this validation possible in excel...........?

If Yes, How ........?,Please....!


Thanks in Advance

Regards
Thyagaraj

 

[Maistrye]

Iam in requirement of a formula in excel for recognising text and
formula for data validation as below.

Validation required:-

1 - The cell should consist of only 10 characters
2 - The first 5 characters should be only text charecters ( eg:
a,b,c........).
3 - The next 4 characters should be only numeric characters ( eg:
1,2,3.....).
4 - The last 10th character should be a text chracter only.

Is this validation possible in excel...........?

If Yes, How ........?,Please....!


Thanks in Advance

Regards
Thyagaraj

This is by no means a complete answer to your question.

If you go to Data -> Validation... then where it says Allow, choose
Custom.

This will check for the length and the middle 4 being numbers: (I'm
assuming this cell is A1)

=IF(AND(LEN(A1)=10, ISNUMBER(MID(A1,6,4))),TRUE,FALSE)

As for checking the remaining characters for their valid values, I'm
not sure what the exact limitations you have on them are. In addition,
I don't really have any ideas on how to check without writing a custom
function to call. Someone else may have to give you ideas on how to
check the other 6 characters. If you figure out a way, just slip it in
the AND(.. , .. , ..).

Scott

 

[Ron Rosenfeld]

Iam in requirement of a formula in excel for recognising text and
formula for data validation as below.

Validation required:-

1 - The cell should consist of only 10 characters
2 - The first 5 characters should be only text charecters ( eg:
a,b,c........).
3 - The next 4 characters should be only numeric characters ( eg:
1,2,3.....).
4 - The last 10th character should be a text chracter only.

Is this validation possible in excel...........?

If Yes, How ........?,Please....!


Thanks in Advance

Regards
Thyagaraj

Here's one method.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr


In some unused cell (eg AA1), enter the formula (assuming you wish to validate
the entry in A1):

=REGEX.COUNT(A1,"^[A-Za-z]{5}\d{4}[A-Za-z]$")

In A1, use the Data Validation formula:

=AA1=1

------------------------------

You need to do it this way as Data Validation formulas must be contained on the
worksheet. The same would hold true for any custom formulas.

The above is a "Regular Expression". The formula counts the number of times
the Regular Expression is found -- in this case we want to see a count of 1.

The regular expression means that A1 must match its pattern exactly in the
order.

^[A-Za-z]{5}\d{4}[A-Za-z]$

^ Start of string
[A-Za-z]{5} Five characters that are letters only
\d{4} Four characters that are digits only
[A-Za-z] One character that is a letter only
$ End of string


--ron