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.
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])”
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.