Eliminate all cells that don't contain a telephone number

[skyport]

I need to be able to eliminate all cells in a column that don't contain a telephone number in the cell. Note: when a telephone number would be in a cell,it would be the only data within that cell. Does anyone have an idea on how this can be done?

 

[Rick Rothstein]

...because of the way each treats the data in the end result.

Are you referring to where our codes output the results to? If so, my code can be modified easily enough to output the results to a different column. Assuming your data is in Column A and the results are to go to Column B...

Sub LeaveOnlyPhoneNumbers() Dim Addr As String Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row Range("B1").Range(Addr) = Evaluate(Replace("IF(LEFT(@)=""("",IF(@="""","""",@),NA())", "@", Addr)) On Error GoTo NoPhoneNumbers Columns("B").SpecialCells(xlConstants, xlErrors).Delete xlShiftUp NoPhoneNumbers: End Sub

 

[skyport]

Rick,

Thanks for the extra mile on this. It is nice to have it both ways because each serve a purpose in a different way under different circumstances. There will be times when each version will be helpful. Thanks again for the great solutions.

 

[hiker95]

To Both Rick and Hiker 95

Both programs work absolutely perfect. There is even a purpose for using them both because of the way each treats the data in the end result.

Thank you both so very much. This is what makes the Excel community so great. It was great to see you both again here on the forum and I also thank both of you for all the great solutions you have contributed over the past year.

If anyone else started a solution and you want me to test it out, I will be happy to give feed back although the problem has been resolved.

skyport

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.