Apply space between numbers

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Code:
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
 
Upvote 0
Hi how about
Code:
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
 
Upvote 0
Thanks,

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


Code:
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
 
Upvote 0
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.
 
Upvote 0
I have supplied the code that also uses WORKSHEET CHANGE EVENT so i need to merge / joing the two together.
That was my question
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top