Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Any way to delete rows that don't contain 'x'?
Hi, I've exported a CSV list of phone calls from Skype into Excel. I want
to delete all rows that do not contain a certain number string e.g. 0000. Is this possible? Ideally it would move other rows up to fill the gaps so I'm just left with a neat list of remaining numbers. -- Thanks. CJSnet (Remove TEETH to reply by e-mail.) |
#2
|
|||
|
|||
Hi
Without using a macro you could do this job with a helper column alongside your data. Put something like this: =COUNTIF(A2:A25,"0000")=0 You can then fill this down. To fix the values in place, copy the range and Edit|Paste Special|Values. You can now sort on this column and delete all the TRUE rows. -- Andy. "CJSnet" wrote in message ... Hi, I've exported a CSV list of phone calls from Skype into Excel. I want to delete all rows that do not contain a certain number string e.g. 0000. Is this possible? Ideally it would move other rows up to fill the gaps so I'm just left with a neat list of remaining numbers. -- Thanks. CJSnet (Remove TEETH to reply by e-mail.) |
#3
|
|||
|
|||
You could even filter them and delete visible rows, no sort.
-- HTH Bob Phillips <Andy B wrote in message ... Hi Without using a macro you could do this job with a helper column alongside your data. Put something like this: =COUNTIF(A2:A25,"0000")=0 You can then fill this down. To fix the values in place, copy the range and Edit|Paste Special|Values. You can now sort on this column and delete all the TRUE rows. -- Andy. "CJSnet" wrote in message ... Hi, I've exported a CSV list of phone calls from Skype into Excel. I want to delete all rows that do not contain a certain number string e.g. 0000. Is this possible? Ideally it would move other rows up to fill the gaps so I'm just left with a neat list of remaining numbers. -- Thanks. CJSnet (Remove TEETH to reply by e-mail.) |
#4
|
|||
|
|||
Thanks, sorry how do I filter?
-- Thanks. CJSnet (Remove TEETH to reply by e-mail.) "Bob Phillips" wrote in message ... You could even filter them and delete visible rows, no sort. -- HTH Bob Phillips <Andy B wrote in message ... Hi Without using a macro you could do this job with a helper column alongside your data. Put something like this: =COUNTIF(A2:A25,"0000")=0 You can then fill this down. To fix the values in place, copy the range and Edit|Paste Special|Values. You can now sort on this column and delete all the TRUE rows. -- Andy. "CJSnet" wrote in message ... Hi, I've exported a CSV list of phone calls from Skype into Excel. I want to delete all rows that do not contain a certain number string e.g. 0000. Is this possible? Ideally it would move other rows up to fill the gaps so I'm just left with a neat list of remaining numbers. -- Thanks. CJSnet (Remove TEETH to reply by e-mail.) |
#5
|
|||
|
|||
Hi, click on the header of the column with the data you want to filter (the one with the 0000 in) Go to Data Filter Autofilter Now click on the dropdown arrow in the header and choose custom From there you have several options to filter for data you want or filter out data you do not. If you filter the data to show what you do not want, you can then delete those rows. HTH, Greg. -- greg7468 ------------------------------------------------------------------------ greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031 View this thread: http://www.excelforum.com/showthread...hreadid=374220 |
#6
|
|||
|
|||
Perfect, thanks.
Also is there a way to say something like 'if A1's value is 'Y' then put '5' in A2 but if A1's value is 'N' then put '0' in A2'? -- Thanks. CJSnet (Remove TEETH to reply by e-mail.) "greg7468" wrote in message ... Hi, click on the header of the column with the data you want to filter (the one with the 0000 in) Go to Data Filter Autofilter Now click on the dropdown arrow in the header and choose custom From there you have several options to filter for data you want or filter out data you do not. If you filter the data to show what you do not want, you can then delete those rows. HTH, Greg. -- greg7468 ------------------------------------------------------------------------ greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031 View this thread: http://www.excelforum.com/showthread...hreadid=374220 |
#7
|
|||
|
|||
Hi Greg, I can't get this working.
When I choose custom, I then specify in the top line 'Contains' and the value '0000'. This should only show rows that contain 0000, however it filters out all rows, e.g. nothing appears. Any ideas how to just show rows that have 0000 in the column I am customising the filter for? -- Thanks. CJSnet (Remove TEETH to reply by e-mail.) "greg7468" wrote in message ... Hi, click on the header of the column with the data you want to filter (the one with the 0000 in) Go to Data Filter Autofilter Now click on the dropdown arrow in the header and choose custom From there you have several options to filter for data you want or filter out data you do not. If you filter the data to show what you do not want, you can then delete those rows. HTH, Greg. -- greg7468 ------------------------------------------------------------------------ greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031 View this thread: http://www.excelforum.com/showthread...hreadid=374220 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to delete blank rows | Excel Discussion (Misc queries) | |||
How can I delete rows from Pivot Tables in Excel 2000 as in 97 | Excel Discussion (Misc queries) | |||
Delete specified critria rows | Excel Discussion (Misc queries) | |||
delete repeating rows | Excel Worksheet Functions | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) |