Welcome Guest
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
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!!

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.

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.

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

 Page 1 of 1