pls help someone

straus

Board Regular
Joined
Oct 7, 2002
Messages
192
I have a list of telephone numbers that have been changed to new ones.
I require a macro where when selecting a particular column/colums and running, the macro should pick up the 1st two digits ( 3 digits in some cases ) of the telephone number and change it in the following format below. Could it be done? Pls anybody help.

thanks a lot in advance
Book23.xls
ABCD
1old numbernew Number
223563
325205
446566
55955595
65965596
75975597
85985598
95995599
1069569
1179559
12
13example
14Old NumberNew number
15230123556301235
16254251420542514
17460521456605214
18595744555957445
19596744755967447
20597847555978475
21598895855988958
22599654455996544
23695262156952621
24790222555902225
25
26
Sheet1
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
So it appears you are keeping the last six numbers of the original number. You can use the RIGHT() text function to extract those. What determines the extra numbers added to the six to create the new number?

Richard
 
Upvote 0
you are right...the last 6 digits will remain the same it is just that the 1st 2 digits will change. I know that this can be done by find>replace. but i want to do it with the help of a macro ( as everytime the sheet gets added with more numbers, I would have to go to find>replace ). Pls help with a macro solution. thanks
 
Upvote 0
You could do it without a macro.

Create a new column called new phone number.

Keep your lookup table separate.

Then use a Vlookup function in combination with left and right functions to populate the new column with the new prefix (the left(rowcol,2) function will give you the lookup value & the right(rowcol,5) will give you the static part of the number.
 
Upvote 0
I require a macro as sometimes I will have to do the process in 2 columns, sometimes 3, sometimes 10 and sometimes 20 columns. It is very tedious inserting a row in the required range everytime the database gets added to new numbers. a macro would ask me to select the range and then i would run the macro to get the numbers changed
 
Upvote 0
I did not realise that it was this large.

You could however parcel the whole process up in to a named range (put the whole formula into the name definition) that assumes the numebr to be converted is one cell to the left.
Same then just use =ConvertNumber to give the result. Same differnce as using a macro though I suppose.
 
Upvote 0
no boss...it does not work that way as the number of rows in the columns keep increasing. a macro will just prompt to select the entire column and then on macro run, the numbers will change. hence i require a macro...anyone pls help. I think, Andrew Poulsom is the right guy for this. if he's anywhere around, pls direct him to my thread...thanks
 
Upvote 0
I saw my name mentioned:

Code:
Sub Test()
    Dim Rng As Range
    Dim c As Range
    Application.ScreenUpdating = False
    If Selection.Rows.Count = ActiveSheet.Rows.Count Then
        Set Rng = Intersect(Selection, ActiveSheet.UsedRange)
    Else
        Set Rng = Selection
    End If
    For Each c In Rng
        If IsNumeric(c.Value) Then
            If Len(c) = 7 Then
            Select Case Left(c.Value, 2)
                Case 23
                    c.Value = 563 & Right(c.Value, 5)
                Case 25
                    c.Value = 205 & Right(c.Value, 5)
                Case 46
                    c.Value = 566 & Right(c.Value, 5)
                Case 69
                    c.Value = 569 & Right(c.Value, 5)
                Case 79
                    c.Value = 559 & Right(c.Value, 5)
            End Select
            Select Case Left(c.Value, 3)
                Case 595 To 599
                    c.Value = 5 & c.Value
            End Select
        
            End If
        End If
    Next c
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
you're such a class act....i knew you would be able to get me out of this....a million, million, million, million, million, thanks
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top