ALPHANUMERIC

[shashidhar]

HI ALL

please help me in formating a column.
I would like to have it look like 001,001A

i.e
three numbers
or
three numbers and one text

how can this be done in data validation ?

 

[Rick Rothstein \(MVP - VB\)]

My guess is there should be a simpler Custom validation formula than the
following one; but, until someone posts it, this will have to do<g>....

=AND(OR(LEN(A1)=3,LEN(A1)=4),ISNUMBER(--LEFT(A1,3)),ISNUMBER(SEARCH(MID(A1,4,1),"abcdefghijklmnopqrstuvwxyz")))

Rick

 

[Harlan Grove]

My guess is there should be a simpler Custom validation formula than
the following one; but, until someone posts it, this will have to
do<g>....

=AND(OR(LEN(A1)=3,LEN(A1)=4),ISNUMBER(--LEFT(A1,3)),
ISNUMBER(SEARCH(MID(A1,4,1),"abcdefghijklmnopqrstuvwxyz")))

....

If A1 contained the text "-.0", that'd produce a TRUE result from your
first ISNUMBER call. You want to test that the first 3 chars in A1 are
NUMERALS *NOT* that they're numeric. There's a difference.

And if the 4th char in A1 were either ? or *, the second ISNUMBER call
would also produce a TRUE result. SEARCH isn't reliable when it's
first argument would be a user entry.

Maybe there's something shorter, but the following exactly satisfies
the OP's specs.

=AND(LEN(A1)<5,LEFT(A1,3)=TEXT(--LEFT(A1,3),"000;;000;"),
NOT(EXACT(LOWER(MID(A1&"z",4,1)),UPPER(MID(A1&"z",4,1)))))

 

[shashidhar]

...

If A1 contained the text "-.0", that'd produce a TRUE result from your
first ISNUMBER call. You want to test that the first 3 chars in A1 are
NUMERALS *NOT* that they're numeric. There's a difference.

And if the 4th char in A1 were either ? or *, the second ISNUMBER call
would also produce a TRUE result. SEARCH isn't reliable when it's
first argument would be a user entry.

Maybe there's something shorter, but the following exactly satisfies
the OP's specs.

=AND(LEN(A1)<5,LEFT(A1,3)=TEXT(--LEFT(A1,3),"000;;000;"),
NOT(EXACT(LOWER(MID(A1&"z",4,1)),UPPER(MID(A1&"z",4,1)))))

thank you
but i did not get answer i require,

sometime i need to enter just the numbers rather than number and
string which this data validation does not allow.

please update me on this

 

[Harlan Grove]

thank you
but i did not get answer i require,

sometime i need to enter just the numbers rather than number and
string which this data validation does not allow.

So your entries could be plain numbers? If so, your original example
of 001 was misleading. Does the entry cell have the number format 000
through which it display 001 when you enter 1? If so, the validation
formula would need to be

=OR(A1=INT(MOD(ABS(N(A1)),1000)),AND(LEN(A1)<5,
LEFT(A1,3)=TEXT(--LEFT(N(A1),3),"000;;000;"),
NOT(EXACT(LOWER(MID(A1&"z",4,1)),UPPER(MID(A1&"z",4,1))))))

This allows entry of whole numbers between 0 and 999, strings of 3
decimal numerals possibly followed by a single Western European
letter. That is, it'll accept accented letters. If you want only
unaccented/English letters, try

=OR(A1=INT(MOD(ABS(N(A1)),1000)),AND(LEN(A1)<5,
LEFT(A1,3)=TEXT(--LEFT(N(A1),3),"000;;000;"),
ABS(CODE(UPPER(MID(A1&"z",4,1)))-78.5)<13))

 

[shashidhar]

i am sorry if i have explained you in wrong way

my data looks somewhat like this
001
001a
002
003
014
015B
078C

and so on
please help me out

thank you