Add City Code to Mobile Phone Numbers Automatically

Home More Excel (VBA) Training Videos

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:
  1. Right-click on your Excel data after selecting it
  2. Select Format Cells
  3. In the Format Cells window under Category select Custom
  4. On the right side of the window under Type select 0 (zero)
  5. Now add  22 the city code and 9 zeros before the existing zero
  6. You can also add a dash between the the 22 and the following zero to get 22-0000000000
  7. 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.
  8. 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 cocatenate the data in the 3 columns or 2 columns using the CONCATENATE function or simply using '&' as the concatenating operator.

Watch this video on YouTube

Home More Excel (VBA) Training Videos