LinkBack URL
About LinkBacks
Hi,
I need to format a list of cells in excels which will accept only alphabets - no numeric or special characters.
I would prefer to do it in a simple method rather than write a complicated code for the same.
Can anyone help please?
Thanks!
Abhinav
Hi,
I need to format a list of cells in excels which will accept only alphabets - no numeric or special characters.
I would prefer to do it in a simple method rather than write a complicated code for the same.
Can anyone help please?
Thanks!
Abhinav
in data validation
create a formula entry
and use
=NOT(ISERROR( SUMPRODUCT( SEARCH(MID( A1,ROW( INDIRECT( "1:"&LEN(A1))), 1), "abcdefghijklmnopqrstuvwxyz" ))))
see cell A1 on attahed
Hi I was just checking out your formula and the sheet you attached.
Through same Data Validation, if we put the following formula:
=istext(range)
That too is working perfectly.
Just check.
=istext(range)
that will only dis-allow numbers
symbols like ? / will be valid and entered - so its not as requested
only alphabets - no numeric or special characters
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
That will allow strings that contain wildcards:Originally Posted by etaf
*
?
X*
?Y
d*g
~~
Try this...
=COUNT(FIND(LOWER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),UPPER(A2)))=0
If you test that formula in a cell on the worksheet it must be array entered.
Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Here's another one that might be slightly more effiicient.
Create this named expression...
Name: Letters
Refers to:
="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
Then, the validation formula becomes:
=AND(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),Letters))
If you test that formula in a cell on the worksheet it must be array entered.
Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
When setting up the validation uncheck: Ignore Blank
Last edited by Tony Valko; 06-29-2013 at 11:20 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks