PDA

View Full Version : Solved: Validation In Cell



Mattster2020
10-29-2009, 10:06 AM
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

lucas
10-29-2009, 10:16 AM
Did you use code or a validation rule?

Mattster2020
10-29-2009, 10:20 AM
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.

lucas
10-29-2009, 10:28 AM
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?

Mattster2020
10-29-2009, 10:42 AM
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

lucas
10-29-2009, 10:46 AM
Is it just one cell or a column?

Mattster2020
11-02-2009, 03:34 AM
Hi Lucas,

Yes the validation is just in one cell.

Cheers,

Matt

Bob Phillips
11-02-2009, 04:20 AM
Maybe this will work as a custom DV test

=AND(ISNUMBER(--LEFT(I1,LEN(I1)-1)),ISTEXT(RIGHT(I1,1)))

Mattster2020
11-02-2009, 04:41 AM
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

GTO
11-02-2009, 05:53 AM
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,

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

Bob Phillips
11-02-2009, 06:23 AM
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

No, that is the formula to put in the data validation as a custom 'Allow' type

GTO
11-02-2009, 06:52 AM
@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,


=ISTEXT(RIGHT(C34,1))

...seems to return TRUE regardless of whether the rightmost character is digit/alpha, or even if the cell is empty.

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:


=AND(ISNUMBER(--LEFT(C34,LEN(C34)-1)),NOT(ISNUMBER(RIGHT(C34,1)+0)))

fall over?

Mattster2020
01-13-2010, 07:47 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

Bob Phillips
01-13-2010, 08:05 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))))

Mattster2020
01-13-2010, 08:08 AM
Thanks xld.

That does the trick.

Regards,

Matt