Patrick

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

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

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

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.