Microsoft Excel. Inserting a common thing to entire row/column

Question

I'm encountering a basic problem with excel and outlook. Please give it a thought.

I have a list of phone numbers on excel sheet as well as outlook. It's just one column. All I need to do is insert '92' before each phone number in every cell.

For instance, at present a cell looks like this; 03128162423

I need it to look like; 9203128162423

Solution 1

Another way to put the "92" in front of the phone numbers is to add column to left and use the following formula:

="92"&B1

copied down through the bottom of the data.

At to doing this in Outlook, this link shows how to modify telephone numbers in the contact list. Although the code is designed to remove prefixes and change formats, it can be easily modified to instead insert a prefix. For example, something as simple as the following function could be applied:

Private Function Add92(strPhone As String) As String
    strPhone = Trim(strPhone)
    If strPhone = "" Then 
        Exit Function
    End If
    Add92 = "92" & strPhone
 End Function

The looping procedure used to go through the list of telephone numbers would then look like this (I have abbreviated the code from the link to focus on the looping structure).

Sub FormatPhoneNumber()
    Dim oFolder As MAPIFolder
    Set oFolder = Application.ActiveExplorer.CurrentFolder
    Dim oItem
    For Each oItem In oFolder.Items
        Dim oContact As ContactItem
        Set oContact = oItem
        If Not oContact Is Nothing Then
            With oContact
                .HomeTelephoneNumber = Add92(.HomeTelephoneNumber)
'                   (the original code includes the other categories
'                    of telephone numbers available in Outlook)             
                .Save
            End With
        End If
    Next
End Sub

Solution 2

If your current values are in column A, put this into column B, starting in cell B1

="92"&A1

copy down.

Comments

[Salaar Khan]

Tried that, it didn't work. Merging only kept the upper left data value in the new merged cell. However Ive come across another way ! simply add 92 before first two rows of the column, select them and copy down the column as far as you like ! lovely feature !

[Salaar Khan]

Thanks anyway. If possible, please suggest a way for outlook. Thanks for your help =]

[Guillaume Rochat]

I currently don't have the office suite on this computer and can't look around for that right now. Sorry about that. You could try recording a macro and rework the code to apply. I've never done VBA in Outlook and I'm not even sure how the phone number are stored in Outlook.