find function

[Yossy]

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

 

[Ashish Mathur]

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

 

[Max]

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,ROWS($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

 

[T. Valko]

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)),ROW(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.

 

[Shane Devenshire]

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.

 

[Yossy]

Thanks to all. I appreciate it.....

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