phone number formate

**akhter1987** · 10-29-2010, 03:37 AM

Some of the numbers in my csv file with only phone numbers look like;

"(111)--111-1111"

and some look like;

"111-111-1111"

Is there a command or function or rule format we can tell the .CSV document to follow to get all the lines to follow the same format style

**Leith Ross** · 10-29-2010, 04:15 AM

Hello akhter1987,

Welcome to the Forum!

Try this first...

Right Click the cell and choose Format Cells...
Under Category click Special
In the Type box click Phone Number
Finally select your Locale from the drop down

**akhter1987** · 10-29-2010, 04:57 AM

Originally Posted by Leith Ross

Hello akhter1987,

Welcome to the Forum!

Try this first...

Right Click the cell and choose Format Cells...
Under Category click Special
In the Type box click Phone Number
Finally select your Locale from the drop down

new to excel don't know these terms

**royUK** · 10-29-2010, 05:21 AM

What terms?

**Marcol** · 10-29-2010, 05:35 AM

Does this work for anything other than English(United States)?

**akhter1987** · 10-29-2010, 05:43 AM

Originally Posted by royUK

What terms?

mean i tried this but its not working what i explain i am using excel 2007

**squiggler47** · 10-29-2010, 05:48 AM

The phone format tends to be US based as all numbers follow the same format!

UK numbers have variable length STD codes 01706-xxxxxx 0161 xxx-xxxx it gets complicated!

**royUK** · 10-29-2010, 06:13 AM

There's not a Special Format for UK based phone numbers, so I think you need to create your own Custom Format.

The numbers in the examole look like they may be text though. It may be simpler to use Find & Replace for the brackets

**squiggler47** · 10-29-2010, 06:22 AM

I suppose the question is what format do you want them in!

the easiest way is to strip out the junk and then re-build the format you want, I am wary about the number formatting option, simply because of leading 0's

=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ",""),"(000)-000-0000")

should cope with the formats you have above and output them as a text string!

Put the formula in a helper column, you can then highlight them and cut and paste using paste special>values, over the originals

**Marcol** · 10-29-2010, 06:44 AM

Maybe this UDF (user defined function)

Option Explicit

Function ReturnAllDigits(ByVal rng, Optional Separator As String = " ")
    Dim n As Integer
    Dim arrDigits As Variant

    ReturnAllDigits = ""

    For n = 1 To Len(rng)
        If Asc(Mid(rng, n, 1)) < 48 Or Asc(Mid(rng, n, 1)) > 57 Then
            Mid(rng, n, 1) = " "
        End If
    Next

    arrDigits = Split(WorksheetFunction.Trim(rng), " ")
    For n = LBound(arrDigits) To UBound(arrDigits)
        If n < UBound(arrDigits) Then
            ReturnAllDigits = ReturnAllDigits & arrDigits(n) & Separator
        Else
            ReturnAllDigits = ReturnAllDigits & arrDigits(n)
        End If
    Next

End Function

To use this
1/. With Excel open Press Alt+f11 this will open the VBa Editor.
2/. In the VBa editor Insert > Module
3/. Paste all of the above code in the resultant pane/window.
4/. Save and close the VBa Editor.

5/. Enter the formula so, if you want to specify a separator

=ReturnAllDigits(A2,"-")

or to accept the default separator (Space)

=ReturnAllDigits(A2)

This will extract the phone number from any string, provided there are no other numbers in the string.

If you can't leave a macro in your workbook then Copy the result and PasteSpecial > Values this will remove the underlying function. You can then remove the code, either Export and Save the module or Delete it if you no longer need it.

Hope this helps