PDA

View Full Version : Not VBA, but a IF & AND problem



ashleyuk1984
01-27-2014, 06:16 AM
Hi,
Firstly, I apologise for not asking a VBA related question, sadly I'm not part of any other community. :crying:
I'm trying to combine ISTEXT and ISNUMBER into one formula... It's sort of working, but then again it isn't.

Lets say for instance, in A1, I have the value "ABC123" ... I want to detect if the first character is a letter, and the last character is a number.


=IF(AND(ISTEXT(LEFT(A1,1)),ISNUMBER(VALUE(RIGHT(A1,1)))),"YES","NO")

This code works. But... then again it doesn't.
If I put this formula into B2, and "ABC123" in A1, then the result would be YES in B2. Which is correct.

If I change the value in A1, to "ABC123ABC", then the result would be NO in B2. Which is correct.

HOWEVER, this is where I'm confused.

If I put "123123123" into A1, then the result would be YES in B2... Which isn't correct ??

Any idea why this is? Or do you have any other formula ideas that would work for this?
Thanks

If the first character is a LETTER, and the last character is a NUMBER, then I want to return TRUE.. Everything else must be FALSE.

Paul_Hossler
01-27-2014, 08:24 AM
I think that RIGHT() returns a string that only happens to look like a number




=IF(AND(LEFT($G$10,1)>="A",LEFT($G$10,1)<="z",RIGHT($G$10,1)>="0",RIGHT($G$10,1)<="9"),TRUE,FALSE)


I'd use 4 parameters in the AND()

It's a little brute force, so maybe some on the WS formulas gurus here will have a more elegant approach

Paul

mikerickson
01-27-2014, 08:54 AM
=ISTEXT("2") will return TRUE, because "2" is text. (=ISTEXT(2) will return FALSE)

Try
=AND(NOT(ISNUMBER(LEFT(A1,1)+0)), ISNUMBER(RIGHT(A1,1)+0))

If you want "{AB123" to return false, then an ASCII testing routine like Paul's will be needed.

ashleyuk1984
01-27-2014, 11:11 AM
Excellent, both of your solutions work perfectly.
I have gone with mikerickson's as I'm placing this into a nest of IF's, so this will look better to the eye :)