Formula to identify text into columns

  • Thread starter Thread starter Forgone
  • Start date Start date
F

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?
 
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?
 
Save As->Text (MS-DOS) .txt
then open the file, in the Import Wizard select Comma in Step 2,
proceed
 
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
 
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
Click to expand...

Cheers Peter,

That did the trick........ Thanks again
 
Save As->Text (MS-DOS) .txt
then open the file, in the Import Wizard select Comma in Step 2,
proceed
Click to expand...

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.
 
Back
Top