Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find function
How do I find certain values in a cells
E.g I want to find values that contain xyz and bring out only values that contain xyz out in another column, the content are not in order and they could come in any way. All help totally appreciated. Thanks col a what_xyz_123 xyz_thursday_under_im my_klm_ply dhyxyz_plum mukl_p123_fjluy_na_dy_xyz The result needed. what_xyz_123 xyz_thursday_under_im dhyxyz_plum mukl_p123_fjluy_na_dy_xyz |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find function
Hi,
Suppose the data above is range B4:B8. In range B3, type any heading, say Strings. In cell B10, type criteria and in B11, enter the following formula =FIND("xyz",B4,1). Now go the Data Filter Advanced Filter and select the radio button for "Copy to another location". In the list range, select B3:B8 and in the criteria range select B10:B11. In copy to, select any blank cell. Now click on OK. Please rememeber that this is not a dynamic solution I.e. if you change any entries in B4:B8, the result will not automatically change. You will have to rerun the advanced filter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Yossy" wrote in message ... How do I find certain values in a cells E.g I want to find values that contain xyz and bring out only values that contain xyz out in another column, the content are not in order and they could come in any way. All help totally appreciated. Thanks col a what_xyz_123 xyz_thursday_under_im my_klm_ply dhyxyz_plum mukl_p123_fjluy_na_dy_xyz The result needed. what_xyz_123 xyz_thursday_under_im dhyxyz_plum mukl_p123_fjluy_na_dy_xyz |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find function
Another option, which delivers results dynamic to source data (assumed in A2
down) In B2: =IF(ISNUMBER(SEARCH("xyz",A2)),ROW(),"") Leave B1 empty In C2: =IF(ROWS($1:1)COUNT(B:B),"",INDEX(A:A,SMALL(B:B,R OWS($1:1)))) Select B2:C2, copy down to cover the max expected extent of source data in col A, say down to C100? Minimize/hide col B. Col C returns the required results neatly packed at the top, dynamic to data changes in col A. If you need the fuzzy search to be a stricter, case sensitive search, replace SEARCH with FIND for the formula in B2. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "Yossy" wrote: How do I find certain values in a cells E.g I want to find values that contain xyz and bring out only values that contain xyz out in another column, the content are not in order and they could come in any way. All help totally appreciated. Thanks col a what_xyz_123 xyz_thursday_under_im my_klm_ply dhyxyz_plum mukl_p123_fjluy_na_dy_xyz The result needed. what_xyz_123 xyz_thursday_under_im dhyxyz_plum mukl_p123_fjluy_na_dy_xyz |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find function
If you want a formula solution try this array formula** :
Assume your data is in the range A2:A6. Enter this array formula** in C2: =IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$6,"*xyz*"),INDEX(A $2:A$6,SMALL(IF(ISNUMBER(SEARCH("xyz",A$2:A$6)),RO W(A$2:A$6)),ROWS(C$2:C2))-MIN(ROW(A$2:A$6))+1),"") Copy down until you get blanks meaning all the data has been extracted. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Yossy" wrote in message ... How do I find certain values in a cells E.g I want to find values that contain xyz and bring out only values that contain xyz out in another column, the content are not in order and they could come in any way. All help totally appreciated. Thanks col a what_xyz_123 xyz_thursday_under_im my_klm_ply dhyxyz_plum mukl_p123_fjluy_na_dy_xyz The result needed. what_xyz_123 xyz_thursday_under_im dhyxyz_plum mukl_p123_fjluy_na_dy_xyz |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find function
Hi,
1. Select the column of data. 2. Chooose Data, Filter, AutoFilter 3. Open the auto filter drop down and choose Custom, pick Contains from the first drop down, enter xyz in the second box. 4. Select the results and copy and paste them to a new location. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Yossy" wrote: How do I find certain values in a cells E.g I want to find values that contain xyz and bring out only values that contain xyz out in another column, the content are not in order and they could come in any way. All help totally appreciated. Thanks col a what_xyz_123 xyz_thursday_under_im my_klm_ply dhyxyz_plum mukl_p123_fjluy_na_dy_xyz The result needed. what_xyz_123 xyz_thursday_under_im dhyxyz_plum mukl_p123_fjluy_na_dy_xyz |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find function
Thanks to all. I appreciate it.....
"Shane Devenshire" wrote: Hi, 1. Select the column of data. 2. Chooose Data, Filter, AutoFilter 3. Open the auto filter drop down and choose Custom, pick Contains from the first drop down, enter xyz in the second box. 4. Select the results and copy and paste them to a new location. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Yossy" wrote: How do I find certain values in a cells E.g I want to find values that contain xyz and bring out only values that contain xyz out in another column, the content are not in order and they could come in any way. All help totally appreciated. Thanks col a what_xyz_123 xyz_thursday_under_im my_klm_ply dhyxyz_plum mukl_p123_fjluy_na_dy_xyz The result needed. what_xyz_123 xyz_thursday_under_im dhyxyz_plum mukl_p123_fjluy_na_dy_xyz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to find a function. | Excel Worksheet Functions | |||
Find Function | Excel Discussion (Misc queries) | |||
FIND function? | Excel Discussion (Misc queries) | |||
Find Function | Excel Worksheet Functions | |||
Find function | Excel Worksheet Functions |