Apply space between numbers

[ipbr21054]

Hi,

Can you advise a basic codce please to add a space between a telephone number.
I have noticed that we have many telephone numbers entered like so,
01934820955 but it shoul;d be entered as 01934 820955

Also same for mobile 07899827427 should be 07899 827427

Information you may require.
Worksheet called DATABASE
Column range in in question is W5 & down the page

It might be a good idea to have this code update as rows will be added constantly.
So when 01934820955 is entered in the cell then leaving the cell will run the code so it then updates to 01934 820955

Have a nice day & many thansk

 

[alansidman]

Option Explicit


Sub splitNr()
    Dim i As Long, lr As Long
    lr = Range("W" & Rows.Count).End(xlUp).Row
    For i = 5 To lr
        Range("W" & i) = Left(Range("W" & i), 5) & " " & Right(Range("W" & i), Len(Range("W" & i) - 5))
    Next i
End Sub

 

[mohadin]

Hi how about

Sub vrers()
    Dim a As Variant
    Dim i
    a = Application.Transpose(Range("w5").Resize(Cells(Rows.Count, "w").End(xlUp).Row - 4))
    For i = 1 To UBound(a)
        a(i) = Left(a(i), 5) & " " & Mid(a(i), Len(Left(a(i), 5)) + 1, 255)
    Next
    Range("Z5").Resize(UBound(a)) = Application.Transpose(a)
End Sub

 

[ipbr21054]

Thanks,

can you advise how the code advised would be entered into the exisiting code that i have.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, DataRange As Range
  
  Set Changed = Intersect(Target, Columns("A"))
  If Not Changed Is Nothing Then
    Set DataRange = Range("A2", Range("A" & Rows.Count).End(xlUp))
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then
          c.Value = c.Value & Format(Evaluate("countif(" & DataRange.Address & ",""" & c.Value & " 0*"")") + 1, " 000")
      End If
    Next c
    Application.EnableEvents = True


  End If
End Sub

 

[mohadin]

Hi
it should go in DATABASE Code
In Excel, right click one of the sheet DATABASE and choose View Code from the pop-up menu. This will open the VBA Editor to the code module associated with that worksheet.

 

[ipbr21054]

I have supplied the code that also uses WORKSHEET CHANGE EVENT so i need to merge / joing the two together.
That was my question

 

[mohadin]

Yes I Guess

 

[ipbr21054]

Whatp

 

[ipbr21054]

What I’m asking is HOW is it done.

 

[mohadin]

Aha OK