Inserting a space between numbers in cell

[ipbr21054]

Morning,

Code supplied below
I added the code below in Red to space out uk post codes of which worked fine.
I then thought to do the same for landline & mobile telephone numbers.
So i then added the code in blue.

I entered 01934874123 & the result was 1934874123

The leading 0 was removed & no space was created between the 4 & 8
The column in question is formated as
Number.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
    Set rng = Intersect(Target, Range("A2:W" & Rows.Count))
    'MsgBox rng.Address
'   Exit if nothing entered into out target range
    If rng Is Nothing Then Exit Sub
   
'   Loop through all cells in our target range
    Application.EnableEvents = False
    For Each cell In rng
        cell = UCase(cell)
    Next cell
    Application.EnableEvents = True
   
    If Target.Column = 4 Then
    On Error Resume Next
    If Len(Target) = 6 Then Target = Left(Target, 3) & " " & Right(Target, 3)
    If Len(Target) = 7 Then Target = Left(Target, 4) & " " & Right(Target, 3)
    End If
    If Target.Column = 3 Then
    On Error Resume Next
    If Len(Target) = 11 Then Target = Left(Target, 5) & " " & Right(Target, 6)
    End If
    On Error GoTo 0
End Sub

 

[Marc L]

Hi,​

​

as the column must be formated as text or with some obvious custom format …​

​

And try Target.Value2 = …​

 

[rlv01]

Have you consider

I entered 01934874123 & the result was 1934874123

The leading 0 was removed & no space was created between the 4 & 8
The column in question is formated as Number.

That's always going to happen if it is formatted as number. Have you considered changing the column format to Text?

 

[ipbr21054]

Thanks,
Changing it to Text worked.

 

[mikerickson]

You could use a format like 000 000 00 00 to show spaces between numerals.