Phone Number Formatting

Hey guys,

I'm new here (However my friend assures me this is the place to come for excel problems and insists you are all geniuses) so apologies if this is in the wrong place but with my limited experience with excel I figured VBA would probably be the best way to do this?

Basically I have a column full of phone numbers that are in varying formats and I need them all in the same format. This is made more complicated by the fact that not all the numbers are UK numbers or even the same length, however a majority are and are currently displayed as either;

+ 44 XXXX XXXXXX
+ 44 XX XXXXXXXX
+ 44 XXX XXXXXXX

Then a range of weird and wonderful other combinations.

and they need to be displayed as;

+ 44 XXX XXX XXXX

If I could get at least the above formats to convert to the way I need them to be displayed then I could do the few remaining manually but it would help massively.

Is VBA the best way to do this? If so where do I start? (I'm relatively new to Excel, let alone VBA)

Thanks a lot guys!

Hey guys,

Thanks for the responses, Please find attached a sample workbook. I left the other columns blank although in my book they are obviouslly full. (There are also 500+ entries as opppsosed to 22)

I've obviously also changed all the phone numbers too but the formats are the same.

Thanks again guys, I appreciate the help.

Your example solution in thread #1 has 17 characters including the spaces. (+ 44 XXX XXX XXXX)
In your attachment, the numbers with a prefix 44 have anywhere from 16 to 19 characters total.
What is the determining factor in how to split it up?
And what is the determing factor for the other numbers?

This will do what you original asked for in thread #1 but it does not take the above mentioned differences in consideration.

Sub TryThis()
    Dim c As Range
    For Each c In Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
        c.Value = WorksheetFunction.Substitute(c.Value, " ", "")
        c.Value = Left(c.Value, 1) & " " & Mid(c.Value, 2, 2) & " " & Mid(c.Value, 4, 3) & " " & Mid(c.Value, 7, 3) & " " & Mid(c.Value, 10, 4)
    Next c
End Sub