+ Reply to Thread
Results 1 to 10 of 10

Check multiple columns to see if they start with certain number patterns

  1. #1
    Registered User
    Join Date
    11-05-2014
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    4

    Check multiple columns to see if they start with certain number patterns

    Hi,

    I have 6 cells in an excel worksheet that I am interested in. I need to check if these 6 cells START with 531, 532, 533 OR 534. If it does start with one of these numbers, input a 1, otherwise input a 0.
    For example, a cell with the value of:
    534929 would give me a 1
    953429 would give me a 0
    3945 would give me a 0
    532 would give me a 1

    So far, I have been able to check one cell if it starts with one pattern of numbers (531 in this case) using this formula:

    =If(ISNUMBER(SEARCH(531,(LEFT(CELLNUMBER,3)))),1,0)

    However, I am unsure how to incorporate more columns/number patterns into this formula.

    Can anyone help me with this?

    Thanks.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Check multiple columns to see if they start with certain number patterns

    Hi.

    =0+OR(LEFT(A1,3)=53&{1,2,3,4})

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,466

    Re: Check multiple columns to see if they start with certain number patterns

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Check multiple columns to see if they start with certain number patterns

    hI, TRY

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Boo?ath?

  5. #5
    Registered User
    Join Date
    11-05-2014
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    4

    Re: Check multiple columns to see if they start with certain number patterns

    My apologies, I forgot to mention that the patterns could potentially include letters, so boop and TMS' solutions don't work.

    XOR LX I think your solution works, but I am wondering how I could make it so it checks multiple cells?

    Sorry if there is a simple solution, I am just not sure how to make the necessary brackets work.

    Thanks again.

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Check multiple columns to see if they start with certain number patterns

    Quote Originally Posted by Mixcels View Post
    XOR LX I think your solution works, but I am wondering how I could make it so it checks multiple cells?
    Not sure what you mean? You just drag the formula down to give equivalent results for strings in A2, A3, etc.

    Regards

  7. #7
    Registered User
    Join Date
    11-05-2014
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    4

    Re: Check multiple columns to see if they start with certain number patterns

    So let's say there are six columns. I want to check all six columns at the same time to see if any column cell starts with 531,532,533,534.

    Only then do I want to drag the formula down to give equivalent results for strings in A2, A3, etc.

    Thanks

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Check multiple columns to see if they start with certain number patterns

    I see. In that case:

    =0+OR(LEFT(A1:F1,3)=53&{1;2;3;4})

    though this will now require entering as an array formula**.

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

  9. #9
    Registered User
    Join Date
    11-05-2014
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    4

    Re: Check multiple columns to see if they start with certain number patterns

    Alright, that seems to work! When I entered it in as a regular formula it kept giving me an error.

    Thanks a lot, you've been a lot of help.

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Check multiple columns to see if they start with certain number patterns

    Of course. Like I said, it has to be entered as an array formula.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Identifying patterns across columns!
    By yellow281 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-29-2012, 12:04 PM
  2. Use Reg Expression to check for 1 of 2 Patterns
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-27-2011, 05:01 PM
  3. Verify matching patterns between two columns
    By okanem in forum Excel General
    Replies: 2
    Last Post: 04-21-2010, 03:33 AM
  4. Help to check patterns in data
    By sg2010 in forum Excel General
    Replies: 0
    Last Post: 03-05-2010, 09:51 AM
  5. How to match data patterns across multiple columns
    By jonathanpc in forum Excel General
    Replies: 9
    Last Post: 04-15-2009, 11:02 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1