Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: IF OR with ISTEXT

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,529
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

    Default 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.

    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Feb 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,529
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

    Default 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.
    Last edited by Joe4; Feb 15th, 2018 at 01:57 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Feb 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    New Member
    Join Date
    Feb 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    Board Regular
    Join Date
    Oct 2013
    Location
    Belo Horizonte, Brazil
    Posts
    695
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default 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.
    Last edited by Marcílio_Lobão; Feb 15th, 2018 at 02:10 PM.

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,516
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default 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))))
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    New Member
    Join Date
    Feb 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    Quote Originally Posted by Marcílio_Lobão View Post
    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. #10
    New Member
    Join Date
    Feb 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

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

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •