I have an excel file which have a worksheet named contact. in contact work sheet in…
- 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.
- 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 SubTo 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.