Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test for "special characters" in text
Hi,
I need to make sure that a cell contains NO SPECIAL CHARACTERS (including the <SpaceBar) during data entry. I've tried: =len(cellReference)=len(substitute(cellReference,o r(char(32),char(34),...etc ),"") but Excel doesn't like the OR funtion (2nd parameter) inside SUBSTITUTE. How do you test if a cell reference contains ANY of the "special text characters" (from list below)? space double quote number dollar percent ampersand apostrophe open parenthesis close parenthesis asterisk plus comma hyphen period forward slash colon semi-colon less than equal greater than at symbol open square bracket backslash close square bracket caret underscore single quote (under tilde) open curly bracket pipe (above backslash) close curly bracket tilde Thanx for your time... (^_^) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test for "special characters" in text
ASAP Utilities, a free Add-in available from www.asap-utilities.com has a
feature called "Advanced Character Removal" that should get rid of any unwanted characters for you.... Vaya con Dios, Chuck, CABGx3 "Frank Cutre" wrote: Hi, I need to make sure that a cell contains NO SPECIAL CHARACTERS (including the <SpaceBar) during data entry. I've tried: =len(cellReference)=len(substitute(cellReference,o r(char(32),char(34),...etc ),"") but Excel doesn't like the OR funtion (2nd parameter) inside SUBSTITUTE. How do you test if a cell reference contains ANY of the "special text characters" (from list below)? space double quote number dollar percent ampersand apostrophe open parenthesis close parenthesis asterisk plus comma hyphen period forward slash colon semi-colon less than equal greater than at symbol open square bracket backslash close square bracket caret underscore single quote (under tilde) open curly bracket pipe (above backslash) close curly bracket tilde Thanx for your time... (^_^) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test for "special characters" in text
Play with something like this array formula:
=MIN(LEN(SUBSTITUTE("ABC#DEFGH",{" ",".","#"},{"","",""})))<Len("ABC#DEFGH") -- Regards, Tom Ogilvy "Frank Cutre" wrote in message ... Hi, I need to make sure that a cell contains NO SPECIAL CHARACTERS (including the <SpaceBar) during data entry. I've tried: =len(cellReference)=len(substitute(cellReference,o r(char(32),char(34),...etc ),"") but Excel doesn't like the OR funtion (2nd parameter) inside SUBSTITUTE. How do you test if a cell reference contains ANY of the "special text characters" (from list below)? space double quote number dollar percent ampersand apostrophe open parenthesis close parenthesis asterisk plus comma hyphen period forward slash colon semi-colon less than equal greater than at symbol open square bracket backslash close square bracket caret underscore single quote (under tilde) open curly bracket pipe (above backslash) close curly bracket tilde Thanx for your time... (^_^) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test for "special characters" in text
Frank Cutre wrote...
I need to make sure that a cell contains NO SPECIAL CHARACTERS (including the <SpaceBar) during data entry. I've tried: =len(cellReference)=len(substitute(cellReference, or(char(32),char(34),...etc .... but Excel doesn't like the OR funtion (2nd parameter) inside SUBSTITUTE. .... Looks like you want only letters and digits. There's no way to prevent users from typing them, and if they're clever enough to know how to use copy & paste, there's no way to use data validation to prevent them from being entered. That leaves removing them yourself or forcing your users to remove them. Doing it yourself, there's no 1-cell way to do it without resorting to VBA. You could use a udf like the following. Function foo(s As String, p As String) As String Dim c As String * 1, k As Long, n As Long n = Len(s) For k = 1 To n c = Mid$(s, k, 1) If c Like p Then foo = foo & c Next k End Function and use it in formulas like =foo(B5,"[A-Za-z0-9]") The other alternative, make your users clean their own entries, can be done by ensuring that invalid entries trigger nothing but errors in formula results along with messages that tell the user that it's their own invalid entries that caused the errors. That can be done with built-in functions and one defined name, e.g., define the name N referring to 128 or so, seq referring to =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,N,1)) then try the *array* formula =0/(COUNT(FIND(MID(UPPER(B5),seq,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))=N) which should return 0 when cell B5 contains nothing but letters and decimal numerals but #DIV/0! when it contains any other characters. In my experience nothing is more effective in motivating users to enter valid data than giving them nothing but errors and diagnostic messages explaining that those errors are due to invalid entries. If a user can only print off pages full of errors and text stating that the errors are their own fault, they can't complain to their bosses or to IT support. All they can do is fix their own errors. This won't win you popularity contests among your users, but it will ensure they do what they're supposed to do. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test for "special characters" in text
Hi Tom,
ref: =MIN(LEN(SUBSTITUTE("ABC#DEFGH",{" ",".","#"},{"","",""})))<Len("ABC#DEFGH") 1) This "was" going in a data validation definition (which doesn't like "arrays") so... how do I get that formula into a VBA udf (which doesn't like the array formula's "{}" curly brackets)? and (just for my own info AND when not used in a data validation definition) 2) How do you include the double quote (") character in the "old_text" parameter of the SUBSTITUTE function? Thanx for your time... (^_^) "Tom Ogilvy" wrote in message ... Play with something like this array formula: =MIN(LEN(SUBSTITUTE("ABC#DEFGH",{" ",".","#"},{"","",""})))<Len("ABC#DEFGH") -- Regards, Tom Ogilvy "Frank Cutre" wrote in message ... Hi, I need to make sure that a cell contains NO SPECIAL CHARACTERS (including the <SpaceBar) during data entry. I've tried: =len(cellReference)=len(substitute(cellReference,o r(char(32),char(34),...etc ),"") but Excel doesn't like the OR funtion (2nd parameter) inside SUBSTITUTE. How do you test if a cell reference contains ANY of the "special text characters" (from list below)? space double quote number dollar percent ampersand apostrophe open parenthesis close parenthesis asterisk plus comma hyphen period forward slash colon semi-colon less than equal greater than at symbol open square bracket backslash close square bracket caret underscore single quote (under tilde) open curly bracket pipe (above backslash) close curly bracket tilde Thanx for your time... (^_^) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test for "special characters" in text
On Tue, 20 Dec 2005 10:19:29 -0800, "Frank Cutre" wrote:
Hi, I need to make sure that a cell contains NO SPECIAL CHARACTERS (including the <SpaceBar) during data entry. I've tried: =len(cellReference)=len(substitute(cellReference, or(char(32),char(34),...etc ),"") but Excel doesn't like the OR funtion (2nd parameter) inside SUBSTITUTE. How do you test if a cell reference contains ANY of the "special text characters" (from list below)? space double quote number dollar percent ampersand apostrophe open parenthesis close parenthesis asterisk plus comma hyphen period forward slash colon semi-colon less than equal greater than at symbol open square bracket backslash close square bracket caret underscore single quote (under tilde) open curly bracket pipe (above backslash) close curly bracket tilde Thanx for your time... (^_^) If you want to use Data Validation, understanding it's limitations that someone could copy/paste a value into that cell that did not meet the criteria, then you could download and install Longre's free morefunc.xll from http://xcell05.free.fr If your cell to be validated is, for example, A1, then in some unused cell (for example Z1) enter the formula: =REGEX.FIND(A1,"\W") This will return a 0 UNLESS there is one of your special characters in A1, in which case it will return the position of that character. Then use the Data Validation Formula Is: =Z1=0 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linked cells and text boxes | Excel Discussion (Misc queries) | |||
dates and text | Excel Discussion (Misc queries) | |||
Find all text instances in a sheet and add one number from each row | Excel Discussion (Misc queries) | |||
How do I test a cell for part of text | Excel Worksheet Functions | |||
change text color based on logical test | Excel Worksheet Functions |