Formula to identify text into columns
 

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?

Formula to identify text into columns
 

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?
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Forgone" wrote:

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?

Formula to identify text into columns
 

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


On 16 Mar, 05:33, Forgone wrote:
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?

Formula to identify text into columns
 

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

On Mar 16, 4:33*am, Forgone wrote:
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?

Formula to identify text into columns
 

On Mar 16, 6:36*pm, Pete_UK wrote:
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

On Mar 16, 4:33*am, Forgone wrote:

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?

Cheers Peter,

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

Formula to identify text into columns
 

On Mar 16, 6:22*pm, Jarek Kujawa wrote:
Save As-Text (MS-DOS) .txt
then open the file, in the Import Wizard select Comma in Step 2,
proceed

On 16 Mar, 05:33, Forgone wrote:

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?

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.

Formula to identify text into columns
 

You're welcome - thanks for feeding back.

Pete

On Mar 17, 12:04*am, Forgone wrote:
Cheers Peter,

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

Formula to identify text into columns
 

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

On 17 Mar, 01:06, Forgone wrote:
On Mar 16, 6:22*pm, Jarek Kujawa wrote:





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

On 16 Mar, 05:33, Forgone wrote:

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?

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.- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -