asked on

question about excel formulas & functions

Hello experts.
I have a column in an excel file which contains 1 to 20 numbers, each separated by a blank (I’ll call this the datacolumn) .
I would like to create some new columns which would have a 1 if a certain number exists in the datacolumn.
For example a new column called yes3 which would have a value of 1 if the number 3 is in the datacolumn, 0 if it is not.
or a new column called yes17 which would have a value of 1 if the number 17 is in the datacolumn, 0 if it is not.
Is this possible in excel using a formula (if or iif ?) in each of the new columns? If not excel then maybe access?
Thank you.
Pat
Example-
Datacolumn yes3 yes17
1 6 7 8 12 17 20 0 1
2 0 0
1 2 3 4 5 6 1 0

Alan_White

Here is a starter for you: =IF(ISERROR(FIND("17",A1)=TRUE),0,1)

You need to IsError bit because FIND will return #Value! if it is not found.

Now the reason that this is not the full solution is what you are searching for. Say you are searching for 7, it will show a 1 if 17 or 27 or 1111171111 is in the line.

So therefore you might what to search for "7 " (seven space). That is better than before but what if there is a seven at the last entry in you data, will that have a space at the end of it? Probably not.

So, I hope I've helped but as I say, it's far from perfect.

ASKER CERTIFIED SOLUTION

Alan_White

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

question about excel formulas &amp; functions

question about excel formulas & functions