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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
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
 

straus

Board Regular
Joined
Oct 7, 2002
Messages
192
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
 

kieran

Active Member
Joined
Oct 27, 2002
Messages
428
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.
 

straus

Board Regular
Joined
Oct 7, 2002
Messages
192

ADVERTISEMENT

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
 

kieran

Active Member
Joined
Oct 27, 2002
Messages
428
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.
 

straus

Board Regular
Joined
Oct 7, 2002
Messages
192

ADVERTISEMENT

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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

straus

Board Regular
Joined
Oct 7, 2002
Messages
192
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

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
Top