| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| identify numbers and text differently in formula | Excel Discussion (Misc queries) | |||
| How to identify same row matches in two columns of #s using Excel | Excel Discussion (Misc queries) | |||
| Identify number of items with characterisitics from two columns | Excel Worksheet Functions | |||
| identify different data in two columns | Excel Discussion (Misc queries) | |||
| identify numbers which are listed in two columns. | Excel Worksheet Functions | |||
Linear Mode
