March 17, 2014

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.

2 thoughts on “Add City Code to Mobile Phone Numbers Automatically

  1. 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 ?

  2. Hello Sir:
    i am working on a project in excel vba, in my project i am required to send text messages(sms) to court attedants from excel work book and also the clerks in the court are required to collect data through a userform. am begging your help on how to send texts from excel to different phone numbers and how to make dynamic combobox ie combobox items change with the change in option box selected.

    thanks in advance


Comments are closed.