Excel question

[amyjayne]

Is there an easy way of finding any cell in a spreadsheet WITHOUT a certain character in it... and removing all entries...?

i.e. any cell without a " � " would be removed?

Any help much appreciated.

:)

[camioneur]

there are 66000 rows and columns going as far as IV on my spreadsheets. Deleting all cells without your references in would simply replace all the cells with empty cells again to maintain the spreadsheet default size. This is a guess as I'm no expert but it stands to reason I think..hope it helps anyway

[amyjayne]

I'm not sure I understand your response...

Deleting all of the cells without a certain reference in is what I need to happen. I have a lot of cells with information in (around 16000) I know that a number of them do not contain a certain character. I need these cells that do not contain the necesary character to be deleted.

It would not matter that they are replaced with empty cells, I can then just sort the data as required... I just need to remove these 'rogue' entries.

[ck1]

If the cell you want to delete only contains the specific character you can use the 'find and replace' function (control F), leave the replace box empty and it will change your character to a blank. If the cell contains multiple characters then you would need to write a short script to delete all cells containing 'x'

[amyjayne]

I'm aware of the 'find' and 'replace' functions.

I need to find cells WITHOUT a certain character in, not cells that contain a certain character in... as I stressed in my first post.

[Machecoul]

Can you give 3 or 4 examples of the data you have in some cells - with and without the symbol?

This might help us find a solution.

I've already had a look at conditional formatting and that doesn't seem to help. Also tried inserting a function but to no avail. It is difficult working in the abstract without some examples.

[amyjayne]

A simple example...

AA0
BB0
CC0
DD0
EE
FF0
GG0
HH
II
JJ0
KK0
LL0
MM0
NN


Those entries that do not contain the character '0' are redundant to me and can be deleted. This is the sort of thing I have on a much grander scale, so you can understand my problem in not wanting to do it individually

[Machecoul]

this might not work but it did for the examples you gave.

Go to spreadsheet and insert a column at column A - this will be empty.
Then go to DATA - select TEXT TO COLUMNS - then FIXED WIDTH - click next. Click to enter lines to separate all the letters in your longest entry. This will put all your data lines in separate columns. You could then sort on the column with 0 in it and delete those without. To recombine the entries i put in cell A1 the following formula =b1&c1&d1 this can then be copied downwards. The cell entries are now recombined.
Two things: this has them in a different order to the original order (could be solved by numbering the entries first) and if you try it - do it with a copy!!!

[amyjayne]

Many thanks for that... really thought I was getting somewhere then, til I realised.... this character is in a different place every time!

Oops... my own fault for giving a crappy example I guess.

You know what? I think i'll just spend a couple of hours doing it manually!

cheers for the help anyway guys

xx