Afternoon All,
I have added validation in cell C34 to only accept a number between 0 - 10000. I now have a new requirement where cell C34 should be able to accept an number first, then a potential letter. For example 21D. the number part of the validation could be resticted to four digits, and only one letter will be required.
Any help would be much appricated.
Regards,
Matt
Did you use code or a validation rule?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I did use a validation rule, I'm guessing I wont be able to do what I want with the validation rule built into Excel. Using code will also be fine.
Quick basic question, do you know these strings ahead of time? In other words can you use data validation to let them pick from a list?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Its basically any number from 1 to 1000 and any lettter in the alphabet. But it would always have to start with a number first, i.e 10A for a building number. I could have two seperate list boxes, one for a number, the other with letters from the alphabet. Then merge the two values together in one cell.
But if there's an easier way that would be useful.
Regards,
Matt
Is it just one cell or a column?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Hi Lucas,
Yes the validation is just in one cell.
Cheers,
Matt
Maybe this will work as a custom DV test
=AND(ISNUMBER(--LEFT(I1,LEN(I1)-1)),ISTEXT(RIGHT(I1,1)))
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks xld,
Ive tried putting the formula into cell C34 (cell that requires validation) but the formula is actually displayed in the cell as text, do I need to change anything?
formula used:
=AND(ISNUMBER(--LEFT(C34,LEN(C34)-1)),ISTEXT(RIGHT(C34,1)))
Cheers,
Matt
Greetings Matt,Originally Posted by Mattster2020
I've read this several times, but am still finding myself confused (not that that's unusual really...).
If you are actually entering the formula in the the cell, you should be getting a circular reference. If you toss it in the cell absent the equals sign, it would be text.
Go to the menubar, Data|Validation. On the Settings tab, in the Allow box, change it from Any Value to Custom. Then in the Formula box that will display, enter XLD's formula.
Hope that helps,
Mark
No, that is the formula to put in the data validation as a custom 'Allow' type
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
@XLD:
Say, I tested the two parts independantly. I was understanding (due in no small part to your repeated explanations) the double unary and all, but the right hand part I wanted to 'get'.
Anyways,
...seems to return TRUE regardless of whether the rightmost character is digit/alpha, or even if the cell is empty.=ISTEXT(RIGHT(C34,1))
Am I missing something?
Mark
PS - How was the game?
Edit: Off to the rack, but in case my observation wasn't goofed, wanted to try\learn. Would:
fall over?=AND(ISNUMBER(--LEFT(C34,LEN(C34)-1)),NOT(ISNUMBER(RIGHT(C34,1)+0)))
Last edited by GTO; 11-02-2009 at 08:08 AM.
Afternoon,
I'm picking this thread up as the validation does not seem to work.
using the below formulas:
=AND(ISNUMBER(--LEFT(C34,LEN(C34)-1)),ISTEXT(RIGHT(C34,1)))
And
=AND(ISNUMBER(--LEFT(C34,LEN(C34)-1)),NOT(ISNUMBER(RIGHT(C34,1)+0)))
The top is the best formula, however it will not accept a single digit number, i.e 1-9, it will accept 1A - 9Z for instance. Not always will a building have 1A, it could just be 1. Can anyone advise where the formula is failing?
Regards,
Matt
Last edited by Mattster2020; 01-13-2010 at 08:00 AM.
It is not failing, it validates for a number and a letter as you originally asked for. Now you have changed the rules.
=OR(ISNUMBER(C34),AND(ISNUMBER(--LEFT(C34,LEN(C34)-1)),ISTEXT(RIGHT(C34,1))))
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks xld.
That does the trick.
Regards,
Matt
Posting Permissions
Reply With Quote
