Formatting Custom Telephone Numbers

[CaptainStansbury]

I want to format a list on US telephone numbers so that a character string of
80055555552265

Would display as
+1.800.555.5555 Ext 2265

This works as long as the Ext is 4 characters long
+1"."###"."###"."####" ""Ext"" "####

But it does not work for an Ext number of less than (or maybe even greater than) four characters.

Any way to have the first 10 characters display as wished and the remaining characters (if any) display as "Ext n"?

 

[mikerickson]

If you enter the number as 8005555555.2265 , you could use
+1"."000"."000"."0000" Ext".######

 

[shg]

Very clever, Mike.

 

[galt13]

If you enter the number as 8005555555.2265 , you could use
+1"."000"."000"."0000" Ext".######

You could try this

=1&TEXT(LEFT(G5,10)," (###) ###-####")&" ext. "&TEXT(RIGHT(G5,LEN(G5)-10),"####")

 

[Rick Rothstein]

I want to format a list on US telephone numbers so that a character string of
80055555552265

Would display as
+1.800.555.5555 Ext 2265

This works as long as the Ext is 4 characters long
+1"."###"."###"."####" ""Ext"" "####

But it does not work for an Ext number of less than (or maybe even greater than) four characters.

Any way to have the first 10 characters display as wished and the remaining characters (if any) display as "Ext n"?

If Mike's solution does not appeal to you, then there is a VB event code solution for what you want to do. To do it, though, we need to specify the column containing your phone number (which I have assumed is Column J in my code below... change it as needed)...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  For Each Cell In Intersect(Target, Columns("J"), ActiveSheet.UsedRange)
    If Len(Cell.Value) < 15 Then
      Cell.NumberFormat = """" & Format(Cell.Value, "!+1\.&&&\.&&&\.&&&& Ext &&&&") & """"
    Else
      Cell.NumberFormat = "General"
    End If
  Next
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.

 

[CaptainStansbury]

Thanks to both of you for taking the time to respond. Both solutions are very clever and both worked for me with very minor tweaks.