Custom Number format, telephone extensions

[mcsbgfam]

Could someone help me out,

I am trying to create a custom number format for phone numbers that will display the extensions. I want something that works like the current special format called phone, with an additonal feature for adding x##

For example
6667777 becomes 666-7777
5556667777 becomes (555) 666-7777
555666777788 becomes (555) 666-7777x88
55566677778888 becomes (555) 666-7777x8888

I tried [>9999999999] (###) ###-####x#### the only problem is that is you have a 2 digit extension instead of a four digit extension it throws the whole thing off.

Thanks for the help guys!

 

[poorwallace]

mcsbgfam,

This isn't quite a format response, but you could try this (I did it mainly as an exercise in using MID and &). Given that your phone number is in cell G11, try this formula. I'm assuming that there are only 4 lengths of phone numbers and they are of uniform quality in that they contain no non-numeric data. So if you've got some phone numbers that have some parenthesis or dashes, this formula will probably screw up. Let's hope this'll work for you.

Wally

=IF(LEN(G11)=7,MID(G11,1,3)& "-"&MID(G11,3,4),IF(LEN(G11)=10,MID(G11,1,0)& "(" &MID(G11,1,3)& ") "&MID(G11,4,3)& "-"&MID(G11,6,4),IF(LEN(G11)=12,MID(G11,1,0)&"("&MID(G11,1,3)&") "&MID(G11,4,3)&"-"&MID(G11,6,4)&"x"&MID(G11,11,2),IF(LEN(G11)=14,MID(G11,1,0)&"("&MID(G11,1,3)&") "&MID(G11,4,3)&"-"&MID(G11,6,4),)&"x"&MID(G11,11,4))))

 

[tactps]

Because you can only hace 3 formats, without VBA you can only do this:
[>999999999999] (###) ###-####x#### ;[>9999999999] (###) ###-####x## ; (###) ###-####

This will do everything you want, except that the first example will show () 666-7777

If you need the formats done, you will need VBA - post back if required.

 

[mcsbgfam]

Thanks both, for the response.

Tactps that worked out great!

 

[poorwallace]

Not that anyone needs or is requesting this, but I was reviewing this and saw that I had made an error in my post...the previous formula doesn't work all the time as it should. I believe this one does.

=IF(LEN(G11)=7,MID(G11,1,3)&"-"&MID(G11,3,4),IF(LEN(G11)=10,MID(G11,1,0)&"("&MID(G11,1,3)&") "&MID(G11,4,3)&"-"&MID(G11,6,4),IF(LEN(G11)=12,MID(G11,1,0)&"("&MID(G11,1,3)&") "&MID(G11,4,3)&"-"&MID(G11,6,4)&"x"&MID(G11,11,2),IF(LEN(G11)=14,MID(G11,1,0)&"("&MID(G11,1,3)&") "&MID(G11,4,3)&"-"&MID(G11,6,4)&"x"&MID(G11,11,4),"TOO FEW OR TOO MANY NUMBERS!!!"))))

 

[Sprucy]

Hi guys & gals,

If the phone numbers go in Column A, were does the formula go?

[>999999999999] (###) ###-####x#### ;[>9999999999] (###) ###-####x## ; (###) ###-####

Under Conditional Formating? I like the idea, just don't know where to add the formula...

Cheers,
Sprucy

 

[XLGibbs]

The goes in as a custom format under Format>Cells>Custom..

 

[Sprucy]

Thanks Gibbs,

Sprucy

 

[lenze]

Hi All:

If you're interested, here is the VBA Approach

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Len(Target) = 7 Then
    With Target
    Application.EnableEvents = False
    .Value = Left(.Value, 3) & "-" & Right(.Value, 4)
    Application.EnableEvents = True
    Exit Sub
    End With
    End If
    If Len(Target) = 10 Then
    With Target
    Application.EnableEvents = False
    .Value = "(" & Left(.Value, 3) & ") " & Mid(.Value, 4, 3) & "-" & Right(.Value, 4)
    Application.EnableEvents = True
    Exit Sub
    End With
    End If
    If Len(Target) = 12 Then
    With Target
    Application.EnableEvents = False
    .Value = "(" & Left(.Value, 3) & ") " & Mid(.Value, 4, 3) & "-" & Mid(.Value, 7, 4) & "x" & Right(.Value, 2)
    Application.EnableEvents = True
    Exit Sub
    End With
    End If
    If Len(Target) = 14 Then
    With Target
    Application.EnableEvents = False
    .Value = "(" & Left(.Value, 3) & ") " & Mid(.Value, 4, 3) & "-" & Mid(.Value, 7, 4) & "x" & Right(.Value, 4)
    Application.EnableEvents = True
    Exit Sub
    End With
    End If
    
End Sub

This goes in the WorkSheet module an assumes the numbers are in column A. Adjust as needed.

HTH

lenze