I have a list of about 65,000 phone numbers which have been entered into the database inconsistently. I would like to have a formula which pulls out only the numbers in the cell. Some examples of how the phone numbers are currently formatted:

(111)111-1111
(111) 111 1111

1111111111
111/111/1111

Etcetera.

Can you help? Thanks!

Andrew

---

Richard Pontificates:

Well, you'd need some VBA code and a custom filtering function to do this RIGHT, but if you only have to do it ONCE, you could use the SUBSTITUTE function and a couple of steps.

Let's say you have your numbers in Column A. You could set up a function in Column B like this:

=SUBSTITUTE(A1,"(","")

This will substitute all ( characters with a blank - effectively removing it. Now, do the same thing for column C to remove the right parentheses:

=SUBSTITUTE(B1,")","")

Keep going for the spaces, slashes, hyphens, dashes, and whatever else you need to remove, and eventually you'll be left with a column of clean numbers. Again it's not the PERFECT solution, but if this is a one-time cleaning job, it will get you by. When you're done, just copy and paste the VALUES from the last column into a fresh sheet, and voila... your clean list of numbers.