help again

[Tina Jones]

hi guys, ive been trying to get some help and i already tried going to other
groups and didnt get an answer that i understood and now my question seems to
be lost in the wind ;o) i hope someone here can help me. i have an exel sheet
that has all kinds of data in it. the sheet was a conversion from a PDF file.
it was a phone bill. what i need to do is find the fastest way to "clear" or
"delete" all the data and text in the sheet "except" the 10 digit phone
numbers. remember its a phone bill so there are many different phone numbers
but all have the "-" in the number like this 000-000-0000
ive hear of ways to clear and or delete everything but colums or rows with
"-" in them but i cant figure out how to do that. i dont know how to use
macros, and formulas and VB i just want to clear everything but the phone
numbers. i have 75 sheets to complete so thats why i need the fastest way,
thanks guys!
-Tina

 

[Biff]

Hi!

Still working on this?

Send me a copy of your file. NOT the PDF file, the XLS file. Is it 75
sheets? Just send 1 sheet.

XL IS NO help at comcast period net

Don't need IS NO and you should be able to figure out the rest.

Biff

 

[Stevie_mac]

Select the cells on each sheet that contains the numbers
Add a new sheet & call it UniquePhoneNos
Open VB (Alt+F11)
Add the macro below to any sheet / module
Press F5 - jobs done.

Public Sub FindPhoneNos()
Dim rResults As Range, sh As Worksheet
Dim rTest As Range
Dim dic, key
Dim OutputSheet As Worksheet
Set dic = CreateObject("Scripting.Dictionary")
Set OutputSheet = Sheets("UniquePhoneNos")

'*******Get Phone Nos********

'loop through sheets
For Each sh In ThisWorkbook.Sheets
'check we are not looking at this sheet
If sh.Name <> OutputSheet.Name And _
sh.Visible = xlSheetVisible Then
'Loop through selected cells
sh.Select
For Each rTest In Selection
'Check see if it is formated as ###-###-###
If rTest.Text Like "???-???-???" Then
'Test see if number has been found already
If dic.Exists(rTest.Text) Then
'YES - inc call count
dic(rTest.Text) = dic(rTest.Text) + 1
Else
'NO - set call count to 1
dic(rTest.Text) = 1
End If
End If
Next
End If
Next

'*******List Results********
'Clear output sheet
OutputSheet.Range("A1", "B65000").ClearContents

'Set rResults to start output at A1
Set rResults = OutputSheet.Range("A1")

'Set up titles
rResults.Value = "Phone Number"
rResults.Offset(0, 1).Value = "Call Count"

'Set rResults to start output at A2
Set rResults = OutputSheet.Range("A2")



For Each key In dic.Keys
'Set cell to Text Format
rResults.NumberFormat = "@"
'Put Phone Number in cell
rResults.Value = key

'Set cell to Text Format
rResults.Offset(0, 1).NumberFormat = "@"
'Put call count in cell
rResults.Offset(0, 1).Value = dic(key)

'refernece next cell (1 row down)
Set rResults = rResults.Offset(1, 0)
Next

'Show results
OutputSheet.Activate
End Sub


let me know if you get stuck.

 

[Tina Jones]

biff thank you again for posting. i tried to emai you and i got an error that
the email is not valid, yes i have a one sheet example. where can i send it?

 

[Tina Jones]

thank you so much but i have no idea what alot of that stuff means!

 

[Biff]

Which address did you use?

Decipher this one:

XL IS NO help at comcast period net

Don't need IS NO and you should be able to figure out the rest.

Biff

 

[Biff]

Hi!

If Tina sends me her file I'll run your macro on it and let you know how it
worked.

Biff

 

[Tina Jones]

biff, thank you, thank you, thank you. how do i send it to you? i tried
clicking your user name and sending an email to you but i got a return
receipt? please let me know. you can email me at mezmerized88-hotmail
thank you so much again!