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.

Leave a Reply

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