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

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

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

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
.

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

"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

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

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

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

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