Welcome Guest
Log In | Register )
You last visited December 5, 2016, 11:30 am
All times shown are
Eastern Time (GMT-5:00)

Need help with excel

Topic closed. 3 replies. Last post 2 years ago by jehb.

Page 1 of 1
PrintE-mailLink
Avatar
New Member
Birmingham al
United States
Member #127165
April 24, 2012
23 Posts
Offline
Posted: April 29, 2015, 6:30 am - IP Logged

Hey all im fairly new to using excel and was hoping someone could help me with a formula to count if a number is in a string.

For example: (9, 21, 23, 31, 35,) are in cell A2. I want to know if a 31 is in this group by getting a "1" if so and a "0" if not.

This is just an example i want to do this for numbers 1-35.

 

Thanks in advance for the help!!

    SergeM's avatar - slow icon.png
    Economy class
    Belgium
    Member #123700
    February 27, 2012
    4035 Posts
    Offline
    Posted: April 29, 2015, 2:53 pm - IP Logged

    A String is a Text in Excel. You have to read the text functions list.

      Avatar
      New Member
      Washington DC
      United States
      Member #165965
      April 29, 2015
      1 Posts
      Offline
      Posted: April 29, 2015, 6:19 pm - IP Logged

      Unfortunately, it is hard to use this text editor to show you a possible solution, and it is not possible to send files.

      Here is one solution:

      Put your numbers on cell A2, without spaces and separated by commas:

      9,21,23,31,35

      Then, put on cells B1 to AJ1, numbers from 1 to 35 (you can write 1 on cell B1, 2 on cell C1 and then, selecting both cells, drag them until AJ1).

      Use the next expression on cell B2 and then drag it until cell J2:

      =IF(ISNUMBER(IF(MID($A$2,1,2)=CONCATENATE(B1,","),1,IF(MID($A$2,LEN($A$2)-1,2)=CONCATENATE(",",B$1),1,SEARCH(CONCATENATE(",",B$1,","),$A$2,1))))=FALSE,0,1)

      Finally, use the following one on cell K2 and drag it until AJ2:

      =IF(ISNUMBER(IF(MID($A$2,1,3)=CONCATENATE(K1,","),1,IF(MID($A$2,LEN($A$2)-2,3)=CONCATENATE(",",K$1),1,SEARCH(CONCATENATE(",",K$1,","),$A$2,1))))=FALSE,0,1)

       

      This will give you what you need.

        Avatar
        New Member
        Birmingham al
        United States
        Member #127165
        April 24, 2012
        23 Posts
        Offline
        Posted: April 30, 2015, 4:48 am - IP Logged

        Unfortunately, it is hard to use this text editor to show you a possible solution, and it is not possible to send files.

        Here is one solution:

        Put your numbers on cell A2, without spaces and separated by commas:

        9,21,23,31,35

        Then, put on cells B1 to AJ1, numbers from 1 to 35 (you can write 1 on cell B1, 2 on cell C1 and then, selecting both cells, drag them until AJ1).

        Use the next expression on cell B2 and then drag it until cell J2:

        =IF(ISNUMBER(IF(MID($A$2,1,2)=CONCATENATE(B1,","),1,IF(MID($A$2,LEN($A$2)-1,2)=CONCATENATE(",",B$1),1,SEARCH(CONCATENATE(",",B$1,","),$A$2,1))))=FALSE,0,1)

        Finally, use the following one on cell K2 and drag it until AJ2:

        =IF(ISNUMBER(IF(MID($A$2,1,3)=CONCATENATE(K1,","),1,IF(MID($A$2,LEN($A$2)-2,3)=CONCATENATE(",",K$1),1,SEARCH(CONCATENATE(",",K$1,","),$A$2,1))))=FALSE,0,1)

         

        This will give you what you need.

        Thanks for the help I will give this a shot!!