Add City Code to Mobile Phone Numbers Automatically

Hello Sir:

I got a few thousand mobile phone numbers from our database. The numbers were from another city. Now I need to send all these numbers messages (SMS) and I cant’t do that without adding the city code, for example, 022. Can I automate the process of adding this city code to the mobile numbers. Please help!
P.S.: Your videos on youtube are very helpful Sir.



View the Excel training video below for details.


VBA code:
Sub addcitycode()
Dim i, LastRow
LastRow = ActiveSheet.Range(“D” & Rows.Count).End(xlUp).Row

For i = 1 To LastRow
Cells(i, 9).Value = “=CONCATENATE(RC[-3],RC[-4],”” – “”,RC[-5])”
Next i

End Sub

The first method using Format cells is implemented as follows:

  • Right-click on your Excel data after selecting it
  • Select Format Cells
  • In the Format Cells window under Category select Custom
  • On the right side of the window under Type select 0 (zero)
  • Now add 22 the city code and 9 zeros before the existing zero
  • You can also add a dash between the the 22 and the following zero to get 22-0000000000
  • Now add another 0 before the first 2 if required, Please note I’m working in Excel 2013. In Excel 2010 it’s the same.
  • Click on OK

The second method without using Excel VBA is to place the mobile phone numbers, the city code and the zero in 3 different columns. Of course you can shorten the process by placing 022, the city code, in another single column.
Now you can concatenate the data in the 3 columns or 2 columns using the CONCATENATE function or simply using ‘&’ as the concatenating operator.

One thought on “Add City Code to Mobile Phone Numbers Automatically

  1. Ashkan Kaan

    We have 4 departments, and I have 30 sheets downloaded from a software to excel on daily basis for each department. I need 3 piece of information from each single sheet. I have created an excel master sheet to calculate the productivity report and variance. How can i automate the process to copy those 3 piece of information to my master sheet.
    The data i get from software is consist of graphs and figures on daily basis.The columns and rows are merged time to time and make it hard to reference it .
    How can I automate the process to copy and paste those information onto my master sheet on daily basis. My master sheet is a monthly data analysis with every day of the month on column and required data on rows..
    i think , i need to put all the 30 sheets on one master sheet and than create micro/vba to automate the process for every single date on the month to my master sheet(Monthly progress report). i dont know how to do it ?


Leave a Reply

Your email address will not be published. Required fields are marked *