finding and selecting certain text

meline12

New Member
Joined
Jan 22, 2004
Messages
10
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Use Advanced Filter with a computed criterion...
Book4
ABCDEF
1XYZY
23reta,yutpmTRUEreta,yutp
36234,890n234,890
43tgfarewqp
51codaq
6
Sheet1


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

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

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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

New Member
Joined
Jan 22, 2004
Messages
10
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

meline12 said:
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
ABCD
1XYZ
23reta,yutpm
36234,890n
43tgfarewqp
51codaq
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
ABCD
1
2TRUE
3XYZ
43reta,yutpm
56234,890n
6
Sheet2
 

meline12

New Member
Joined
Jan 22, 2004
Messages
10
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
meline12 said:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,321
Messages
5,769,444
Members
425,546
Latest member
DisMissive

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top