Hi,
I am a relative beginner with Excel.
Latest problem:
I have a list of over 50,000 phone no's with other data.
Phone no's are in one column, but up to 10% are invalid because they have fewer than the required no of dogits.
In this case, for mobile ph. no's (beginning with leading zero), the 10 digits are usually formatted:
(example) 0412 369 852.
I could easily change the format to eliminate the spaces:
(0412369852)
How do I "filter out" the invalid no's with less than 10 digits?
Any help much appreciated.
Peter O

I first would probably find and replace all the spaces in that column. Find the spaces and basically leave the replace section blank. I then would add another column to your excel spreadsheet which will have the LEN() function in it. This will check the number of characters in the other column. You can then sort the new column and weed out the numbers that are wrong. If you want to restrict the format they type the phone numbers in then I might recommend you convert this over to an MS Access database and use the input mask to restrict formatting.

Not a problem what I was doing is essentially the same minus a few steps and adding some others. Then Len() was just telling me how many characters were in the cell making it easier for me to find the ones with missing characters. I saw you question was unanswered from way back when and thought I would give you an assist if you still need it. I also use it as a way of having people search and find answers for their future questions. Finally could you please mark the post as solved via the "thread tools" drop-down list at the top of the post? Thanks in advance.

jimr,
Thanks for the reply.
As you might expect I have become a little more accomplished in Excel & I ws shown a way of doing what I wanted:
First save the file as a CSV file.
Then reopen the newly created CSV file.
Apply the filter to the phone no column & choose "Sort - smallest to largest"
(if I do not first save & reopen the sort options are A to Z etc which is unsuitable).
It is then relatively easy to scroll down until you find the "below 10 digit no's"
Delete the first "below 10 lot", then copy into new workbook the balance of the file.
This proceedure seems to work regardless of the actual formatting so long as it is consistent.
There are actually quite a few steps involved but it seems intuitive now. Also I always feel strongly the need to double check afterwards that the names associated with th phone no's remain correctly asociated when the job is completed. This takes as long as the sort itself.
I will try your approach & compare.
If you wish to comment on the above please do so.
Thanks again,
Peter O