Any way to delete rows that don't contain 'x'?

[CJSnet]

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.)

 

[Guest]

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.

 

[Bob Phillips]

You could even filter them and delete visible rows, no sort.

 

[CJSnet]

Thanks, sorry how do I filter?
--
Thanks.

CJSnet

(Remove TEETH to reply by e-mail.)

 

[greg7468]

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.

 

[CJSnet]

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.)

 

[Bob Phillips]

=IF(A1="Y",5,0)

--
HTH

Bob Phillips

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.)

message news:[email protected]...

 

[CJSnet]

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.)

 

[CJSnet]

Hi guys, 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.)

 

[Gord Dibben]

Is the '0000' actually in the cells or is it a custom format?

Filtering only operates on what is in the cell, not what it is formatted to
look like.


Gord Dibben Excel MVP

 

[CJSnet]

Hi, it's actually in the cells.

Lets say I have 2 lots of phone numbers:

A1 = 02012345678
A2 = 020123456780000

I want to only show the cells like A2 with '0000' in.

If you look at the above thread I have tried all of that and explained the
problem below.
--
Thanks.

CJSnet

(Remove TEETH to reply by e-mail.)