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

 

[ipbr21054]

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

Hi,
Ive gone with your code and merged it to the existing worksheet change event,hope its correct.

When i enter a mobile number in the cell then i leave the cell i see a RTE 13 type mismatch.
when i debug the line below is in yellow.

Range("W" & i) = Left(Range("W" & i), 5) & " " & Right(Range("W" & i), Len(Range("W" & i) - 5))

Tel numbers are always in column W
current range is W5 & down the page

 

[ipbr21054]

Sorry forget above post.

I have merged your code with the existing change event in use.

See below but when i leave the cell the space isnt created

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


    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 If
End Sub

 

[alansidman]

My code is a loop to cycle through column W to change the spacing. I don't understand what the events in column A have to do with doing a loop in Column W. Your original post only referred to column W. Suggest you tell us the whole story. In this manner, we can help to solve your issue instead of trying to piecemeal your issues.

 

[ipbr21054]

Hi,
I’m looking for a code like what you advised so when I leave the cell the space is added etc.

I already have a change event so I added yours into it as you can’t have two change events.

So what I put above was the two put together.

 

[ipbr21054]

My code is a loop to cycle through column W to change the spacing. I don't understand what the events in column A have to do with doing a loop in Column W. Your original post only referred to column W. Suggest you tell us the whole story. In this manner, we can help to solve your issue instead of trying to piecemeal your issues.

Do you know understand why column A is mentioned ?

It was in use before and now ive added your piece of code to it

 

[ipbr21054]

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

Hi,

Your codes works BUT

I type in a cell in column W 01234567891 when i leave the cell i then see in the cell to the right in column Z 012345 67890

So it adds the space when i leave the cell but in cell Z and not W

 

[mohadin]

Range("[COLOR=#00ff00]W5[/COLOR]").Resize(UBound(a)) = Application.Transpose(a)

 

[Peter_SSs]

Would just formatting the numbers with a space be suitable?
Select column W by clicking its heading label then go into Format cells and apply a custom format of 00000 000000

 

[ipbr21054]

Would just formatting the numbers with a space be suitable?
Select column W by clicking its heading label then go into Format cells and apply a custom format of 00000 000000

Perfect & so quick and easy

Thanks

 

[Peter_SSs]

Perfect & so quick and easy

Thanks

You're welcome. Often, simplest is best.