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

  • Thread starter Thread starter andreashermle
  • Start date Start date
A

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
 
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
 
andreashermle said:
I got a column of 10-digit numbers. They need
to be converted like this .....
Before: xxxxxxxxxx (10 digits)
After: xx-xxx-xx-xxx
Click to expand...

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 -----
 
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 -
Click to expand...

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
 
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 -
Click to expand...

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
 
Back
Top