Thanks:  0
Likes:  0

# Thread: IF OR with ISTEXT

1. ## IF OR with ISTEXT

I'm trying to write a formula that says if the 8th character in my string (from the left) is either the number 1 or a letter, then return the left 8 characters, if not, return the left 7. However, when I use this it always returns 8 characters no matter what. What am I doing wrong? (I'm entering this formula in cell A1):

=IF(OR(LEFT(B1,8)=1,ISTEXT(LEFT(B1,8))),LEFT(B1,8),LEFT(B1,7))

Thank you!

2. ## Re: IF OR with ISTEXT

Welcome to the Board!

LEFT(B1,8)
actually returns 8 characters, not 1

If you want to return the 8th character only, use:
MID(B1,8,1)

Armed with this new knowledge, see if you can update your formula.

3. ## Re: IF OR with ISTEXT

I want to return all 8 if the 8th character is the number "1" or a letter. If not, then I want it to return the first 7 characters.

4. ## Re: IF OR with ISTEXT

I want to return all 8 if the 8th character is the number "1" or a letter. If not, then I want it to return the first 7 characters.
I understand. But you are checking one character (the 8th character) in your condition, and then deciding whether to return 7 or 8 characters based on that.
So, in your two conditions in your OR clause, you need to change that to use the MID function like I showed instead of LEFT.
You would would continue to use LEFT in your TRUE and FALSE clauses after that.

5. ## Re: IF OR with ISTEXT

Also, I thought maybe it was the first part of the formula, so I changed it, but it still only returns 8 instead of 7

=IF(OR(MID(B1,7,1)=1,ISTEXT(MID(B1,7,1))),LEFT(B1,8),LEFT(B1,7))

6. ## Re: IF OR with ISTEXT

ANd same if I do this:

=IF(OR(MID(B1,8,1)=1,ISTEXT(MID(B1,8,1))),LEFT(B1,8),LEFT(B1,7))

7. ## Re: IF OR with ISTEXT

Chialiwa, Good afternoon.

Joe4, Greetings from Brazil.

Try to use:

=IF(OR(--MID(B1,8,1)=1,MID(B1,8,1)>=CHAR(65)),LEFT(B1,8),LEFT(B1,7))

Is thaat what you want?

I hope it helps.

8. ## Re: IF OR with ISTEXT

Here is a slightly different approach you can try...

=LEFT(A1,8-(ISNUMBER(0+MID(SUBSTITUTE(A1,1,"X"),8,1))))

9. ## Re: IF OR with ISTEXT

Hi Marcilio,

That works for the ones with the letters, but not for the ones with the number 1 as the 8th character. I ran a test formula on my data and I think the problem is that it is picking up all of the 8th characters as text (even though there is just a number there). Not sure how to fix that.

Originally Posted by Marcílio_Lobão
Chialiwa, Good afternoon.

Joe4, Greetings from Brazil.

Try to use:

=IF(OR(MID(B1,8,1)=1,MID(B1,8,1)>=CHAR(65)),LEFT(B1,8),LEFT(B1,7))

Is thaat what you want?

I hope it helps.

10. ## Re: IF OR with ISTEXT

Rick! Thank you! This worked! I'm not sure how (this formula is more complex than I can understand), but it does work!

Originally Posted by Rick Rothstein
Here is a slightly different approach you can try...

=LEFT(A1,8-(ISNUMBER(0+MID(SUBSTITUTE(A1,1,"X"),8,1))))