LinkBack URL
About LinkBacks
Search A2 to make sure doesn't contain Alphabet or Special character. Please help!
Search A2 to make sure doesn't contain Alphabet or Special character. Please help!
Do you mean that you need data validation for numeric only?
Just use data validation formula =ISNUMBER($A$2)
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Do you mean doesn't contain the word 'alphabet' or doesn't contain any letter FROM the alphabet. And what special character are you interested in. We're pretty good at lots of stuff but we're not psychics.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Could you check if the cell is numeric??
Formula:
Please Login or Register to view this content.
1. Does not contain alphabet or special characters [A-Z],[!@#$%^&*()_+-=\;:’”,.<>/?]
If the contents in a cell are neither letters nor symbols, that only leaves numbers, correct?
If you want to check to see if the cell contains anything other than a number, you can use this:
=IF(ISERROR(A2+0),"Does Contain Them","Does Not Contain Them")
That works for the alphabet but what if I want to check the length of characters too. Anything greater than 8 equal No else Yes
I'm not sure who you are responding to. Maybe this?
=IF(OR(ISERROR(A2+0),LEN(A2)>8),"No","Yes")
Last edited by 63falcondude; 02-20-2018 at 03:19 PM. Reason: Missed the ) after 8.
... if validation formula
=ISNUMBER($A$2)*(LEN($A$2)<9)
If standard formula...
=IF(AND(ISNUMBER($A$2),LEN($A$2)<9),"GOOD","BAD")
Neither formula worked. It says I have too many arguments.
Whoops, sorry:
=IF(OR(ISERROR(A2+0),LEN(A2)>8),"No","Yes")
That worked!Originally Posted by 63falcondude
Great!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
For row 6 it should have been a "No" because it contains a character. I can't figure out how to attach the spreadsheet, but I have a value A2345678. If it doesn't contain all numbers, then it should be "No". I don't want any alphabet or special characters. Or anything greater or less than 8
The formula from post #11 does return No with A2345678 in A2.
To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"
using 63falcondude's formula on A2345678 I get "No"
if you want to post a sheet go to the "go advanced" below the reply window, then midway down is manage attachments and click on that and follow the instructions.
one hint I found is it is best to save your sheet to the desktop as it is easier to grab from there when using browse.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Also,
Post #7:
Post #14:
You're changing your criteria. Which is it?
Capture.PNG
Please see the attachment
I think you changed his formula from OR to AND.
EDIT: that isn't the formula 63FD gave you.
Where did that formula come from? That is not the same as any of the suggested formulas!
Also, you uploaded a picture instead of an Excel workbook.
Looks like you combined my formula and 63falcondude's formula.
Mine checks for both being TRUE, 63falcondude's formula checks if one or the other condition is FALSE.
Hence, order of "GOOD","BAD" and "NO","YES" is reversed in IF formula.
If there is an alphabet or number length greater or less than 8, I want it to say "No". The attachment button isn't allowing me to attach the file
See formula in post #11...
You still haven't answered my question from post #17.
Do you mean to say that if number in cell is not equal to 8, then it should return "No"?
Then change my formula to...
=IF(AND(ISNUMBER($A$2),LEN($A$2)=8),"Yes","No")
63falcondude's formula to...
=IF(OR(ISERROR(A2+0),LEN(A2)<>8),"No","Yes")
@Sonya
To save several people lots of time skating around this would you upload the workbook please - you have been asked.
Be sure to manually add the results you expect to see for different permuations of your data.
The paperclip attach icon isn't working. Click on the Go Advanced button and then go to the 'Manage Attachments' option underneath the posting panel.
Last edited by Richard Buttrey; 02-22-2018 at 01:42 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
,
Bookmarks