Conversion of 10 digit number into xx-xxx-xx-xxx

[andreashermle]

Dear Experts:

I got a column of 10-digit numbers. They need to be converted like
this .....

Before: xxxxxxxxxx (10 digits)
After: xx-xxx-xx-xxx (10 digits separated by hyphens)

I would like to run a macro that does this conversion on selected
cells.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

 

[EricG]

Try this. It will allow for multiple selections anywhere on the sheet. It
only addresses the first column of each selection, though. Enter the macro
in a general code module in the Visual Basic Editor.

Option Explicit

Sub Convert_Phone_Numbers()
Dim nAreas As Long, nRows As Long, i As Long, j As Long
Dim oldText As String, newText As String
'
nAreas = Selection.Areas.Count
'
For i = 1 To nAreas
nRows = Selection.Areas(i).Rows.Count
For j = 1 To nRows
oldText = Selection.Areas(i).Cells(j, 1).Text
newText = Left(oldText, 3) & "-" & Mid(oldText, 4, 3) & "-" &
Right(oldText, 4)
Selection.Areas(i).Cells(j, 1) = newText
Next j
Next i
End Sub

HTH,

Eric

 

[Joe User]

I got a column of 10-digit numbers. They need
to be converted like this .....
Before: xxxxxxxxxx (10 digits)
After: xx-xxx-xx-xxx

Are the original contents ("before") bona fide numbers (i.e. TYPE(A1)=1), or
they strings (text) of digits (i.e. TYPE(A1)=2)?

If they are bona fide numbers, and if it is sufficient for them to simply
appear as you wish, you might try using the Custom format 00-000-00-000 .

If they are text, it might be sufficient for you to put the following
formula into a parallel column: =TEXT(A1,"00-000-00-00") . If you wish,
you can replace the original column with the results of the new column by
copying the new column, then using paste-special-value to overwrite the
original column. Then you can delete the new column.

Finally, if you truly wish to use a macro, perhaps the following will do the
trick. Select all of the cells with 10-digit numbers. Then execute the
following macro:

Option Explicit
Sub doit()
Dim c As Range
For Each c In Selection
c = Format(c, "00-000-00-000")
Next c
Selection.Columns.AutoFit
End Sub


----- original message -----

 

[andreashermle]

Try this.  It will allow for multiple selections anywhere on the sheet. It
only addresses the first column of each selection, though.  Enter the macro
in a general code module in the Visual Basic Editor.

Option Explicit

Sub Convert_Phone_Numbers()
    Dim nAreas As Long, nRows As Long, i As Long, j As Long
    Dim oldText As String, newText As String
'
    nAreas = Selection.Areas.Count
'
    For i = 1 To nAreas
        nRows = Selection.Areas(i).Rows.Count
        For j = 1 To nRows
            oldText = Selection.Areas(i).Cells(j, 1).Text
            newText = Left(oldText, 3) & "-" & Mid(oldText,4, 3) & "-" &
Right(oldText, 4)
            Selection.Areas(i).Cells(j, 1) = newText
        Next j
    Next i
End Sub

HTH,

Eric










- Show quoted text -

Hi Eric,

great coding. Exactly what I wanted. Had to alter the code slightly to
suit my needs. Thank you very much for your professional help.
Regards, Andreas

 

[andreashermle]

Are the original contents ("before") bona fide numbers (i.e. TYPE(A1)=1), or
they strings (text) of digits (i.e. TYPE(A1)=2)?

If they are bona fide numbers, and if it is sufficient for them to simply
appear as you wish, you might try using the Custom format 00-000-00-000 .

If they are text, it might be sufficient for you to put the following
formula into a parallel column:  =TEXT(A1,"00-000-00-00") .  If youwish,
you can replace the original column with the results of the new column by
copying the new column, then using paste-special-value to overwrite the
original column.  Then you can delete the new column.

Finally, if you truly wish to use a macro, perhaps the following will do the
trick.  Select all of the cells with 10-digit numbers.  Then execute the
following macro:

Option Explicit
Sub doit()
Dim c As Range
For Each c In Selection
c = Format(c, "00-000-00-000")
Next c
Selection.Columns.AutoFit
End Sub

----- original message -----











- Show quoted text -

Hi Joe,

thank you very much for the macro and non-macro solution. Both work
just fine. Thank you very much for your professional help. Regards,
Andreas