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

 

[Rick Rothstein]

This macro will handle everything... it removes the spaces (if any) from the original data and then inserts the spaces where you want them. Note... you did not say where your phone numbers were located at, so I assumed Column A starting at Row 1, change the red highlighted part as needed...

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

 

[worksop]

Thanks Rick that looks just what I want. Will try it tommotom.

 

[worksop]

Hi Rick, I have tried it out but unfortunately all the numbers are converted to 0000, 000, 000.
Is there a way to can I use active cell or some other reference?.
Thanks in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

If you have already stripped out the spaces, see how this one goes.

Sub FormatPhoneNumbers1()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",text(#,""00000 000 000""))", "#", .Address))
  End With
End Sub

If you want the code to do the 'space-stripping' as well as the formatting on your raw data, try

Sub FormatPhoneNumbers2()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",text(substitute(#,"" "",""""),""00000 000 000""))", "#", .Address))
  End With
End Sub

 

[worksop]

Hi Peter_SSs.
the sub formatphonenumber2 work a treat please see below.
(I installed Mrexcell HTML Maker, followed the instructions the Mr excell tab was displayed but the diaspeared is that normal?.
Anyway THANKS for YOUR HELP>


After Befor
20831 111 144 0208 31111144
02072 325 636 0 20 7232 5636
01233 621 839 01233 621839
01622 726 262 01622 726262
07774 591 470 07774 591470

 

[Peter_SSs]

Hi Peter_SSs.
the sub formatphonenumber2 work a treat please see below.

That's great, but I note that the first example contains 12 digits whereas I us under the impression that your numbers were all 11 digits. Is that 12-digit example correct and, if so, how should that number be formatted as we cannot do 5-3-3?

(I installed Mrexcell HTML Maker, followed the instructions the Mr excell tab was displayed but the diaspeared is that normal?.

I suspect that you may not have taken note of this from the 'Attachments' link page:

N.B. With any of the Add-Ins with recent versions of Windows, you may need to "unblock" the file. In Windows Explorer, find the file, right-click, Properties, General tab, tick the 'Unblock' box down near the bottom ..

 

[worksop]

Yes found unblock, now shows on the menu. THANKS.

 

[Ingolf]

This macro will handle everything... it removes the spaces (if any) from the original data and then inserts the spaces where you want them. Note... you did not say where your phone numbers were located at, so I assumed Column A starting at Row 1, change the red highlighted part as needed...

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

Rick,
your code puts spaces correctly at the first number (A1) and then copies that number to the entire A column.
You can check, please. Thank you.

 

[Rick Rothstein]

Rick,
your code puts spaces correctly at the first number (A1) and then copies that number to the entire A column.
You can check, please. Thank you.

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