Dear Experts:

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

Befo 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" wrote:

Dear Experts:

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

Befo 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
.

"andreashermle" wrote:
I got a column of 10-digit numbers. They need
to be converted like this .....
Befo 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" wrote in message
...
Dear Experts:

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

Befo 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

On Jan 18, 11:48*pm, EricG wrote:
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" wrote:
Dear Experts:

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

Befo 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
.- Hide quoted text -

- 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

On Jan 19, 12:20*am, "Joe User" <joeu2004 wrote:
"andreashermle" wrote:
I got a column of 10-digit numbers. They need
to be converted like this .....
Befo 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" wrote in message

...



Dear Experts:

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

Befo 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- Hide quoted text -

- 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