Creating Inputbox with value both numeric and limited alphabetb

0

Hello, 

I am trying to create an inputbox which just have limit with 11 characters such as 12x1250x2500. 

I tried several methods however each time I couldnt get my solution. 

Is there anyway to build up a method or it is not possible to create?

Thank inadvance.

Answer
Discuss

Answers

0

Creating an input box with restrictions as to what the user is permitted to enter is comparatively easy with VBA. I had a feeling that you omitted to mention VBA because you don't really want it. Here is a solution that works without VBA, just using Data Validation.

  1. Select the cell you wish to validate.
  2. From the Data tab, select Data Validation > Data Validation.
  3. In the Allow field, select Custom.
  4. Copy the formula below and paste it to the Formula field.
    =AND(NOT(OR(ISERR(VALUE(LEFT(A1,2))),ISERR(VALUE(MID(A1,4,4))),ISERR(VALUE(MID(A1,9,4))))), AND(EXACT(MID(A1,3,1),"x"),EXACT(MID(A1,8,1),"x"),LEN(A1)=12))
  5. Modify the formula to replace all occurrences of "A1" with the address of the cell you are validating. You can apply absolute addressing if you wish to copy the formatting of the validated cell.
  6. Then press OK.
  7. Copy the validated cell to other cells if you need to validated more than one cell.

The formula has several components. The first checks if the two characters on the left translate into a numeric value.

ISERR(VALUE(LEFT(A1,2)))
This function will return True if there is an error. That is the opposite of what the validation requires: True if there is no error. So, the three tests are connected with OR and then reversed with NOT. The group returns False if any of the three evaluates to True, meaning if any of them isn't numeric.

Note that only the first ISERR function evaluates a LEFT string. The other two look at MID strings but they work identically.

EXACT(MID(A1,3,1),"x")
returns True if MID(A1,3,1) is exactly "x". This is case sensitive. If you wish to permit a capital X change the formula to (MID(A1,3,1)="x") which allows upper or lower case. There are two "x" in your result. Therefore this formula is repeated twice, selecting different characters in the string. These formulas return True if there is no mistake. Therefore there is no need to reverse their output.

The same holds true for the last bit. 

LEN(A1)=12
This is still necessary because if a user were to enter "12x2500x3500AAA" the entry would be accepted. This last function returns True only if the total length is exactly 12 characters. True is what the validation needs. Therefore this function is thrown in with the two EXACT functions and linked with them in an AND bracket.

The two formulas - NOT OR() and AND() - are combined into one AND() function and that returns False if the pattern isn't exactly what you are prescribing.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login