Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to Macro help PLEASE
I have a column of telephone numbers. I need to change the area code and first three digits of each of these numbers to a new area code and new first thee digits. Example: 5555555555 change to (444)444-5555. notice the last four digits need to stay the same. If I have 100 numbers to change, the last 4 digits will be 100 unique numbers. The formula =value(444444&right(a1:a100,4)) works however I need to do this as part of a Macro ( for ease to the person using the spreadsheet ) need some help PLEASE !!! thanks -- Hemming ------------------------------------------------------------------------ Hemming's Profile: http://www.excelforum.com/member.php...o&userid=32185 View this thread: http://www.excelforum.com/showthread...hreadid=520559 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to Macro help PLEASE
Enter this macro:
Sub Macro1() Dim r As Range Dim s1 As String Dim s3 As String s3 = "(444)444-" For Each r In Selection s1 = r.Value r.Value = s3 & Right(s1, 4) Next End Sub Then on the worksheet, select the cells you want to convert and run the macro -- Gary's Student "Hemming" wrote: I have a column of telephone numbers. I need to change the area code and first three digits of each of these numbers to a new area code and new first thee digits. Example: 5555555555 change to (444)444-5555. notice the last four digits need to stay the same. If I have 100 numbers to change, the last 4 digits will be 100 unique numbers. The formula =value(444444&right(a1:a100,4)) works however I need to do this as part of a Macro ( for ease to the person using the spreadsheet ) need some help PLEASE !!! thanks -- Hemming ------------------------------------------------------------------------ Hemming's Profile: http://www.excelforum.com/member.php...o&userid=32185 View this thread: http://www.excelforum.com/showthread...hreadid=520559 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to Macro help PLEASE
You have other suggestions at your other posts.
Hemming wrote: I have a column of telephone numbers. I need to change the area code and first three digits of each of these numbers to a new area code and new first thee digits. Example: 5555555555 change to (444)444-5555. notice the last four digits need to stay the same. If I have 100 numbers to change, the last 4 digits will be 100 unique numbers. The formula =value(444444&right(a1:a100,4)) works however I need to do this as part of a Macro ( for ease to the person using the spreadsheet ) need some help PLEASE !!! thanks -- Hemming ------------------------------------------------------------------------ Hemming's Profile: http://www.excelforum.com/member.php...o&userid=32185 View this thread: http://www.excelforum.com/showthread...hreadid=520559 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add a formula using a macro | Excel Worksheet Functions | |||
Need a formula/Macro | Excel Discussion (Misc queries) | |||
Formula or Macro help?... | Excel Discussion (Misc queries) | |||
Sum Formula in Macro | Excel Programming | |||
Using the And formula in a VBA macro | Excel Programming |