Insert a space into phone numbers

[worksop]

Hi, I am new to VBA and trying to get to grips with it.
I have a large list of phone numbers that are in different formats i.e. 01234 456 789, or 01234 456789 ect.
I have managed to clear all spaces out to leave continuous numbers, now I want to insert a space after 5th digit of the area code and 3rd digit of the number i.e. 01234 245 567.
Is there a way insert spaces in the current cell? with out Concatenate or copying to other cells.
Thanks

 

[Peter_SSs]

This revised code should work correctly...

Though to me it is not inserting spaces where requested and also fills any blank cells (if there are any) with 'zero' numbers.

 

[Ingolf]

Yep, you are correct. Because TEXT is a text string function, you must induce array handling into it which I forgot to do. Thanks for catching that. This revised code should work correctly...

Sub FixPhoneNumbers()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Replace " ", "", xlPart, , , , False, False
    .Value = Evaluate("[B][COLOR=#ff0000]IF({1},[/COLOR][/B]TEXT(" & .Address & ",""0000 000 0000"")[B][COLOR=#ff0000])[/COLOR][/B]")
  End With
End Sub

Now it's OK. Thanks for revised code.


@Peter_SSs

Though to me it is not inserting spaces where requested and also fills any blank cells (if there are any) with 'zero' numbers.

For me Rick's code work excellent.

 

[Peter_SSs]

For me Rick's code work excellent.

Neither of my points were massive issues & can easily be altered to produce the same result as my earlier code that the OP says "work a treat", but for you does the code ..

- If there are blank cells in the original data, leave them blank or introduce the new phone number 0000 000 0000

- Format the numbers as requested?

now I want to insert a space after 5th digit of the area code and 3rd digit of the number i.e. 01234 245 567

or does it format them differently like this
0123 424 5567

 

[worksop]

Thanks Rick, does work well, I have used it to update are suppliers list, all uniform now.
THANKS