finding and selecting certain text

[meline12]

i want to find and select text within a certain column that contains a comma in them. Please help. I have a large database that i would like to extract all the cells (within the 2nd column) that have commas in them.

 

[Aladin Akyurek]

Use Advanced Filter with a computed criterion...

Book4
	A	B	C	D	E	F
1	X	Y	Z			Y
2	3	reta,yutp	m		TRUE	reta,yutp
3	6	234,890	n			234,890
4	3	tgfarewq	p
5	1	coda	q
6
Sheet1

The formula in E2 in the Criteria range E1:E2 is...

=ISNUMBER(FIND(",",B2))

 

[Cbrine]

You could also paste this formula beside the first entry, copy down and do a sort on the column.

=IF(ISERROR(FIND(",",A1)),"","Has Comma")
A1 being the address of the data, if yours is different you will need to adjust.
Hope this helps

 

[meline12]

thanks so much for the responses. Aladin, i tried your formula, and it returned true/false to those with or without a comma, but im a little fuzzy on how to use the advanced filter for this situation. I need another sheet to copy down the WHOLE row of each one name that has a comma in it. Does this make sense? i appreciate your help

 

[Aladin Akyurek]

thanks so much for the responses. Aladin, i tried your formula, and it returned true/false to those with or without a comma, but im a little fuzzy on how to use the advanced filter for this situation. I need another sheet to copy down the WHOLE row of each one name that has a comma in it. Does this make sense? i appreciate your help

Suppose what follows is the data of interest (source)...

Sheet1

Book4
	A	B	C	D
1	X	Y	Z
2	3	reta,yutp	m
3	6	234,890	n
4	3	tgfarewq	p
5	1	coda	q
6
Sheet1

Sheet2 (destination)

Leave A1 empty.
In A2 enter:

=ISNUMBER(FIND(",",Sheet1!B2))

Select A1:A2 on Sheet2.
Activate Data|Filter|Advanced Filter.

Click OK if necessary.
On Advanced Filter dialog window:

Check Copy to another location.
Enter Sheet1!$A$1:$C$5 in the box for List range.
Enter $A$1:$A$2 or Sheet2!$A$1:$A$2 in the box for Criteria range.
Enter $A$3 or Sheet2!$A$3 in the box for Copy to.
Leave Unique records only unchecked.
Click OK.

Sheet2 will look now like this...

Book4
	A	B	C	D
1
2	TRUE
3	X	Y	Z
4	3	reta,yutp	m
5	6	234,890	n
6
Sheet2

 

[meline12]

for some reason, i still cant get this process to work. here is part of my Sheet1, called Master. I want to put it into another sheet called TestCode. I need columns A through H from Master to be copied onto a sheet called TestCode (of course only those in Column B with commas in them). Master sheet has a total of 3763 rows. I'm sorry to keep bothering you, but i really want to figure this out. Thanks!

Column A Column B Column C
2230476009 Roxburgh R M & G J 7618 S 700 E
2230476016 Roxburgh R M & G J 7618 S 700 E
2230477005 Nelson, Donna S 7720 S 700 E
2230477016 Nelson, M Don & Donna S 7720 S 700 E
2230477017 Nelson, Donna S 7738 S 700 E
2230477018 Nelson, Donna S 7708 S 700 E
2230478006 Lofgren, Robert K 657 E 7800 S
2230478005 Stonebrook Real Estate 655 E 7800 S
2230478001 Candlelite Ents 642 Candlelite Ln
2230478002 Skog, Roy A 652 Candlelite Ln
2230478003 Smith, Lawrence P 660 Candlelite Ln
2230477007 Nelson, M Don & Donna S 7720 S 700 E 2230477009 Butler, E C & Barbara 653 Candlelite Ln [/list][/quote]

 

[Aladin Akyurek]

for some reason, i still cant get this process to work. here is part of my Sheet1, called Master. I want to put it into another sheet called TestCode. I need columns A through H from Master to be copied onto a sheet called TestCode (of course only those in Column B with commas in them). Master sheet has a total of 3763 rows. I'm sorry to keep bothering you, but i really want to figure this out.

The only thing that I can add is that columns A to H in Master must each have a label, distinctly formatted, e.g, in bold and italic.

Create the Criteria range in TestCode. The formula there must refer to the first B-cell on Master which house data (not the label). From here on, just try to follow the instructions, adjusting the range values.