Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ALPHANUMERIC
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 ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ALPHANUMERIC
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),"abcdefghi jklmnopqrstuvwxyz"))) Rick "shashidhar" wrote in message ... 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 ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ALPHANUMERIC
"Rick Rothstein" wrote...
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),"abcdefghijklmnopqrst uvwxyz"))) .... 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))))) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ALPHANUMERIC
On Jan 28, 4:41*am, Harlan Grove wrote:
"Rick Rothstein" wrote... 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),"abcdefghijklmnopqrst uvwxyz"))) ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ALPHANUMERIC
shashidhar wrote...
On Jan 28, 4:41 am, Harlan Grove wrote: .... =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. 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)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ALPHANUMERIC
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add alphanumeric data? (e.g., 1C+2D) | Excel Discussion (Misc queries) | |||
sort alphanumeric | Excel Discussion (Misc queries) | |||
alphanumeric sorting | Excel Worksheet Functions | |||
alphanumeric formating | Excel Discussion (Misc queries) | |||
aLPHAnUMERIC validation | Excel Worksheet Functions |