Nothing but the numbers

i have an excel file with phone numbers in it that have variations of dashes and parentheses, spaces, periods, etc.. I need to be able to change all the phone numbers to be just the individual phone numbers crunched together with no dashes, periods, spaces, or parentheses. please advise on how to eliminate everything but the numberss

thx,
Bobby Hesley.
bobhesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Add this code to a general module, select the cells to convert, and run the macro.

Public Sub PurgeChars()

' Removes specific characters from all cells in the selected area.

   Dim Cell As Range
   Dim OldValue As String
   Dim NewValue As String
   Dim Index As Long
   
   ' Set UseLeaveChars to True to use LeaveChars, False to use DeleteChars
   Const UseLeaveChars = True
   Const LeaveChars = "[0-9]"
   Const DeleteChars = ""

   ' To include a dash or hyphen (-), place first or after exclamation mark. To
   ' match a left bracket ([), question mark (?), number sign (#), and asterisk
   ' (*), enclose them in brackets. The right bracket (]) can't be used within
   ' a group to match itself, but it can be used outside a group as an
   ' individual character.
   
   If TypeName(Selection) <> "Range" Then Exit Sub

   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False
   
   For Each Cell In Application.Selection
      If Len(Cell) > 0 Then
         NewValue = ""
         OldValue = Cell
         For Index = 1 To Len(OldValue)
            If UseLeaveChars Then
               If Mid(OldValue, Index, 1) Like LeaveChars Then NewValue = NewValue & Mid(OldValue, Index, 1)
            Else
               If Not Mid(OldValue, Index, 1) Like DeleteChars Then NewValue = NewValue & Mid(OldValue, Index, 1)
            End If
         Next Index
         Cell = NewValue
      End If
   Next Cell
   
   Application.EnableEvents = True
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True

End Sub

To add VBA code to a regular module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bobhesAuthor Commented:
I appreciate your answer, but I don't understand any of this. I'm totally new to excel. I don't know macro order general function or anything like that. I just need to know how to eliminate dashes, etc.. Please give me some simple steps I can understand

thx,
Bobby

Here's my original question:
 i have an excel file with phone numbers in it that have variations of dashes and parentheses, spaces, periods, etc.. I need to be able to change all the phone numbers to be just the individual phone numbers crunched together with no dashes, periods, spaces, or parentheses. please advise on how to eliminate everything but the numberss

thx,
Bobby Hesley.

zorvek (Kevin Jones)ConsultantCommented:
If you not willing to follow the directions above to add and use a macro, you are going to have to use the find and replace function for each of the characters you want to remove...

Select the cells to edit. Press CTRL+H and enter the character to remove in the first text entry box. Clear the second text entry box. Click Replace All. Repeat for each character you want to remove.

Kevin
Patrick MatthewsCommented:
Hi bobby,

For text parsing, I like this swiss-army-knife-like function:




Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True)

    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll controls
    ' whether all instances of the matched string are replaced (True) or just the first instance (False)
   
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
   
    ' If you use this function from Excel, you may substitute range references for all the arguments
   
    Dim RegX As Object
   
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
    End With
   
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
   
    Set RegX = Nothing
   
End Function


After adding it, use it like this:

=RegExpReplace(A1,"\D","",TRUE)

That returns a string of nothin' but numerals.  To return it as an actual number:

=VALUE(RegExpReplace(A1,"\D","",TRUE))

Regards,

Patrick
jijojacCommented:
Please mention whether you want to do it manually or programatically. In case to do programatically the above answers will help you else you can use replace from the edit menu.

find /
replace blank
and so on for everything.

Select the column in which numbers are present.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.