Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· openaiHugging Face Suzhen/CodeChat

I have an excel file which have a worksheet named contact. in contact work sheet in…

  1. User

    I have an excel file which have a worksheet named contact. in contact work sheet in column B I have phone numbers of my customer. phone numbers stored in different format. I want a macro code that create a new worksheet with correct phone number format. phone number must be 10 digits. some phone number start with country code 46 that I would like to omit this. also some phone number start with 0 that I also like to omit this. I need the macro to omit less than 10 digit phone numbers. I want to copy corrected phone number and the customer name stored in column A to the new worksheet. I need the macro that do these jobs. please give me complete code.

  2. Assistant

    Here is a complete VBA macro code to perform the requested task.

    Sub clean_phone_numbers()
    
        ' Declare variables
        Dim ws As Worksheet
        Dim new_ws As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim phone As String
        Dim name As String
    
        ' Set the original worksheet
        Set ws = Worksheets("contact")
    
        ' Create a new worksheet
        Set new_ws = Worksheets.Add
    
        ' Find the last row with data in the original worksheet
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
        ' Loop through rows in the original worksheet
        For i = 1 To lastRow
            ' Get the name and phone number
            name = ws.Cells(i, 1).Value
            phone = ws.Cells(i, 2).Value
    
            ' Check if the phone number starts with country code (46) and remove it
            If Left(phone, 2) = "46" Then
                phone = Right(phone, Len(phone) - 2)
            End If
    
            ' Check if the phone number starts with 0 and remove it
            If Left(phone, 1) = "0" Then
                phone = Right(phone, Len(phone) - 1)
            End If
    
            ' If the phone number is 10 digits, copy it to the new worksheet
            If Len(phone) = 10 Then
                new_ws.Cells(new_ws.Cells(new_ws.Rows.Count, "A").End(xlUp).Row + 1, "A").Value = name
                new_ws.Cells(new_ws.Cells(new_ws.Rows.Count, "B").End(xlUp).Row, "B").Value = phone
            End If
        Next i
    
        MsgBox "Phone numbers have been cleaned and copied to a new worksheet.", vbInformation, "Done"
    
    End Sub
    

    To use this code, press ALT + F11 to open the VBA editor, and then insert a new module by clicking on "Insert" > "Module". Copy and paste the code above into the new module, and then close the VBA editor.

    Finally, press ALT + F8 to open the "Macro" dialog in Excel, select "clean_phone_numbers", and click on "Run". The macro will create a new worksheet containing the cleaned phone numbers along with the customer names.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.