VBA 'Function' to clean up UK format numbers

[Read_This]

Guys, I wonder if you can help?

I using a VBA Funtion to clean up UK phone numbers to the required '01234 567890' format.
I'm currently using three different steps to get the result - I'm looking for all three steps to be combined into one VBA function script - ideally added to the existing function below.

The current VBA function and first step is;
============================
Function CleanUpNumber(StrNumber)
Dim StrNoSpaces, StrNoPluses, StrNoBracketsA, StrNoBracketsB, StrFinalNumber
StrNoSpaces = Replace(StrNumber, " ", "")
StrNoPluses = Replace(StrNoSpaces, "+", "0")
StrNoBracketsA = Replace(StrNoPluses, "(", "")
StrNoBracketsB = Replace(StrNoBracketsA, ")", "")
If Left(StrNoBracketsB, 3) = "044" Then
StrFinalNumber = "0" & Right(StrNoBracketsB, Len(StrNoBracketsB) - 3)
Else
StrFinalNumber = StrNoBracketsB
End If
CleanUpNumber = StrFinalNumber
End Function
============================

I then use;

=REPT(0,11-LEN(A1))&A1

as a second step to get a leading zero [if required], and then;

=REPLACE(A1,6,0," ")

as a third step to add a space character for the correct '01234 567890' format
[ the raw number above being in Cell A1 ]

Below is a sample of the raw data.

+44 8802192025
08802 798559
08802798559
8815698257

Any help/pointer would be appreciated.

 

[portews]

See if this criteria fits; remove the spaces and take the right 10 digits and then format it with a space between the 4th and 5th digits and a zero at the front.

Sub formatPhone()For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(i, 1) = Format("0" & Right(Replace(Range("A1"), " ", ""), 10), "00000 000000")
Next
End Sub

You can do it with a formula and custom cell formatting (same format as above) if that works for you.

=0&RIGHT(SUBSTITUTE(A1," ",""),10)

 

[Rick Rothstein]

You can do it with a formula and custom cell formatting (same format as above) if that works for you.

=0&RIGHT(SUBSTITUTE(A1," ",""),10)

As long as you are going to use a formula, why not let it do everything instead of parceling some of it out cell formatting?

=TEXT(RIGHT(SUBSTITUTE(A1," ",""),10),"00000 00000")

 

[Read_This]

Brilliant ! Much appreciated