Formula to identify text into columns

[Forgone]

I've been given a question in excel and I think it can be done but
can't figure out how to do so.

The worksheet they have has in Column A - a description of a certain
object in Column B it lists the stations that the object was found and
seperated by commas.

EG:

Object 1 | 1,2,3,4,5,6,7,8,10,11,13,15,16,17,18
Object 2 | 2,3,6,7,8,10,11,13,18
Object 3 | 5,15

What they want to do is to have each station as a column.....

So the headings would be......

Object, Stations Found, 1,2,3,4,5,n,20

I started doing a formula =FIND($C$1,$B3,1)
but when it got to Object 2, it found 1 in position 11 because of the
number 11.....

How am I able to search by "exact" within that array?

 

[JBeaucaire]

This function is builtin to the DATA menu. It's called TEXT TO COLUMNS.

Highlight the data, select Data > Text to Columns > Delimited > Comma > FINISH

That should get you close, yes?

 

[Jarek Kujawa]

Save As->Text (MS-DOS) .txt
then open the file, in the Import Wizard select Comma in Step 2,
proceed

 

[Pete_UK]

Use this instead in C3:

=IF(ISNUMBER(FIND(","&C$1&",",","&$B3&",")),"x","")

It puts commas around the number you are looking for, and also puts
commas around the string of numbers in column B, so that you can look
for exact matches. It returns a grid of "x" under each appropriate
number when copied across and down.

Hope this helps.

Pete

 

[Forgone]

Use this instead in C3:

=IF(ISNUMBER(FIND(","&C$1&",",","&$B3&",")),"x","")

It puts commas around the number you are looking for, and also puts
commas around the string of numbers in column B, so that you can look
for exact matches. It returns a grid of "x" under each appropriate
number when copied across and down.

Hope this helps.

Pete

Cheers Peter,

That did the trick........ Thanks again

 

[Forgone]

Save As->Text (MS-DOS) .txt
then open the file, in the Import Wizard select Comma in Step 2,
proceed

I think, could be wrong, but wouldn't the Data > Text to Columns do
the same trick? It wasn't exactly what I was looking for but still
sincerely appreciated.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

 

[Jarek Kujawa]

yep, exactly, yr solution sounds even simpler
thks for the feedback