Hello,
How do I do data validation/cell formatting so that only alphabetic characters can be entered and no numbers?
(We use the cell for First Name, Last Name, etc. and I don't want potential entries like Mr. 56565 55454 because the cells are formatted as text and allow alphabet and numbers.)
I tried =NOT(ISNUMBER(H6)) for Data Validation, but that didn't work.
??
VR/Lost
Last edited by leaning; 10-07-2010 at 06:13 PM. Reason: Not completely solved!
Try perhaps custom formula:
=A1=LEFT(A1,MIN(FIND(0,SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},0)&0))-1)
where A1 is top-left most cell in your selection to validate.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
NBVC,
I missed something somewhere.
If the user is to input "Smith" into A1, where do I put your formula? As the Data Validation >Custom of A1?
?
VR/Lost
Yes. The formula goes in the cell of the validation.. adjust the cell reference if necessary.
NBVC,
Sorry to be a bother.
For cell A1, Data Validation>Custom.
Paste =LEFT(A1,MIN(FIND(0,SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},0)&0))-1)
I get the error You may not use unions, intersections, or array constants for Data Validation criteria.
What am I doing wrong?
VR/Lost
Try =AND(ISERROR(FIND(ROW(INDIRECT("1:10")) - 1, A1)))
Last edited by shg; 10-07-2010 at 05:53 PM. Reason: Oops ...
Entia non sunt multiplicanda sine necessitate
Re: Allow only letters in cell (no numbers)?
shg,
That did it! Thanks!
(But I don't know what I am going to do if 50 Cent becomes one of our customers....)
I love this group!
VR/Lost
shg,
I spoke too soon.
???&%# can still be entered.
I guess I need to rename my post "Allow only letters in cell (and nothing else)"
Can you do something to your formula like "A:Z" like you did "1:10" to not allow characters like ?$%, etc. ?
?
VR/Lost
Getting uglier
=AND(MIN(CODE(MID(UPPER(A1), ROW(INDIRECT("1:" & LEN(A1))), 1))) > 65, MAX(CODE(MID(UPPER(A1), ROW(INDIRECT("1:" & LEN(A1))), 1))) < 90)
another alternative might be
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Well (harrumph), my formula has the distinct advantage of ... uh ... oops, phone is ringing, gotta go...
EDIT -- but you should still put the INDIRECT in there ...
@shg - why do you say that ?Originally Posted by shg
incidentally I'm not advocating mine over others - it's not efficient in it's own right - though (at present) it's non-volatile...
Well, I wouldincidentally I'm not advocating mine over others
Because without it, if you insert rows above row 90, the formula changes.why do you say that ?
shg,
I tried your code, but it doesn't allow "was", "fats" etc.
(I don't know enough about how your formula is constructed to edit it.)
So, "smith" and "jones" work fine; "richards" doesn't.
??
VR/Lost
Sorry, the comparson operators should include =:
=AND(MIN(CODE(MID(UPPER(A1), ROW(INDIRECT("1:" & LEN(A1))), 1))) >= 65, MAX(CODE(MID(UPPER(A1), ROW(INDIRECT("1:" & LEN(A1))), 1))) <= 90)
But I like DO's better:
=SUM(LEN(A1) - LEN(SUBSTITUTE(UPPER(A1), CHAR(ROW(INDIRECT("65:90"))), ""))) = LEN(A1)
Last edited by shg; 10-07-2010 at 08:40 PM.
And a smack on my wrists.. for not thinking about my formula giving that error... I didn't test it in data validation, I just came up with the formula and assumed "wrongly" that it would work in the data validation.. but data validation doesn't allow the array {...} ...
Fair point... I didn't think of that ... knowing me though I'd have used INDEX:INDEX
In case anyone comes across this thread as I did, you can additionally limit the cell to a specific number of characters, which was my need.
=and(len(a1)=2,SUM(LEN(a1) - LEN(SUBSTITUTE(UPPER(a1), CHAR(ROW(INDIRECT("65:90"))), ""))) = LEN(a1))
A simpler but imperfect solution is:
=and(istext(a1),len(a1)=2).
The flaw is that it allows spaces and special characters to be entered.
Have a blessed day.
PS - If you also want to force them to be capital letters, use:
=and(Exact(A1,upper(a1)),len(a1)=2,SUM(LEN(a1) - LEN(SUBSTITUTE(UPPER(a1), CHAR(ROW(INDIRECT("65:90"))), ""))) = LEN(a1))
Last edited by Bert Uschold; 07-24-2014 at 04:13 PM. Reason: Add additional info.
The above formula is EXACTLY what I need ... I only need one minor adjustment.
Is there any way at all to do this same exact thing, but allow spaces?
Meaning: In a "Last Name" Field, the Entry 'SMITH-JONES' would return ERROR but 'SMITH JONES' is allowed
I need numbers and special characters to remain on lockdown but need to allow the space between a hyphenated name.
Is this possible?
Thank you!
If your data in "A1"
Try with this in "B1"Formula:
Please Login or Register to view this content.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Hello avk & Guys! thank you. the last post is great and the formula works nicely.
Last edited by saudi_red_neck; 06-13-2019 at 02:43 AM.
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks