Phone Number Format

[dictatordane]

Hi everyone,

Sorry if this is an easy one. I have a very long list of phone numbers. They are currently in the following format:

02 1234 5678

I want to change this so they look like:

02 12345678

I want the cell to include the 0 at the front (not just look like it) so when you click on the cell it should show the 0 in the front in formula bar.

Some phone numbers in the database are different. Example:

13 1234

In all cases I want 2 numbers, a space and the rest of the numbers.

Any help would be much appreciated.

Kind regards,

Dane

 

[Trebor76]

Hi Dane,

Change the 'lngRowStart' and 'strMyCol' variables (if necessary) and then run the following macro while on the tab with the data in question:

Sub Macro1()

    Dim lngRowStart As Long, _
        lngRowEnd As Long
    Dim strMyCol As String
    Dim rngCell As Range, _
        rngMyDataSet As Range
        
    lngRowStart = 2 'Assumed start row - change to suit.
    strMyCol = "A" 'Assumed data resides in Col A - change to suit.
    
    lngRowEnd = Cells(Rows.Count, strMyCol).End(xlUp).Row
    Set rngMyDataSet = Range(strMyCol & lngRowStart & ":" & strMyCol & lngRowEnd)
    
    Application.ScreenUpdating = False
    
    For Each rngCell In rngMyDataSet
    
        With rngCell
    
            If Len(.Value) > 0 Then

                .NumberFormat = "@" 'Text
                .Value = Replace(.Value, " ", "")
                .Value = Left(.Value, 2) & " " & Mid(.Value, 3, Len(.Value) - 2)
                
            End If
            
        End With
                
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

HTH

Robert