Working with phone number

[a4aniruddh]

Hi,

I am a beginner in VB. I need help in phone number formatting in excel VBA. Below is my requirement.
My requirement is.
I am getting phone numbers in one sheet in row for eg A1:A50
I need to prefix std codes if needed. for example if number is 25736305 from bangalore. I need to add 080. Final final output should be 08025736305. If it is already prefixed then no need to prefix once again.
Also it should validate the number. That is after adding std the number length should not exceed 10 digits. Because in India the phone number length is 10. I need that invalid length number in different sheet or workbook and remove from original.

Request your help on this on how to achieve this on excel vba.

 

[VaraK]

Hi,

Here is the code that looks if the string length <> 11. Phone number in India including STD is 11 digits (inclusive of zero at the start). If you do not have zero in the front change the code in the If condition.

Sub InvldPhs()
Dim d As Double, g As Double
Dim ws As Worksheet
Dim wss As Worksheet

'change this if your sheet name is not Sheet1
Set ws = Sheets("Sheet1")
Set wss = Sheets("Sheet2")
'change accordingly if your column is not column A
d = Application.WorksheetFunction.CountA(ws.Range("A:A"))
g = 2

For d = 1 To d
If Len(ws.Cells(d, 1)) <> 11 Then
wss.Cells(g, 1) = ws.Cells(d, 1)
ws.Cells(d, 1) = ""
g = g + 1
End If
Next d
ws.Cells.Sort key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess
End Sub


Another important thing is I'm not moving adjacent columns to sheet2. if you want that we can customize accordingly.

Let me know if you need further help.

 

[a4aniruddh]

Thanks for the help. For checking the length it is working fine. Please help me in adding std codes. I have std codes. That need to be prefixed with number then it should check for number length.
Thanks,
Aniruddh

 

[VaraK]

Is it 080 to all the phone numbers? or do u have a list of cities and std codes located somewhere?

 

[a4aniruddh]

I have a list of city with std codes. also please provide code move complete raw into next sheet(adjacent) and remove from the original data.

Thanks for your help.

Thanks,
Aniruddh

 

[VaraK]

Hi

Sorry, this is still not clear.

Where do you have the std codes saved? in the same workbook different sheet, another workbook?

Do you have the city name in your sheet1 where you want to prefix the code?

Am I right if I say, in the phone numbers there would be std codes already prefixed for some and for some you want to prefix?

Would there be any case where you will have a city in sheet1 but in your std code list that city is not available?

And which are the ones you want to move to adjacent sheet?

I think it would be better if you provide with an example data set.

 

[a4aniruddh]

Hi,

Sorry for the delay in providing the data. I was not keeping well. So i was hospitalized.

Coming to example data

I have excel file called std codes. Which contains only std codes of different cities like mentioned below.
Column A Column B
Bangalore 080
Mangalore 0824
Mysore 0821
Chennai 044
Hyderbad 041

Another is my actual my requirement file with following data
<table x:str="" style="border-collapse: collapse; width: 154pt;" border="0" cellpadding="0" cellspacing="0" width="205"><col style="width: 48pt;" width="64" span="2"> <col style="width: 58pt;" width="77"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">Customer name</td> <td style="width: 48pt;" width="64">City</td> <td style="width: 58pt;" width="77">Phone number</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Abc</td> <td>Bangalore</td> <td x:num="" align="right">25736304</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">cde</td> <td>Mangalore</td> <td x:num="8242658789" align="right">8242658789</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">fgh</td> <td>Mysore</td> <td x:num="" align="right">32456
</td></tr></tbody></table>
Here for the first one i need output as 08025736304(11 digits)
2.08242658789(11 digits)
3. The number is invalid. So it should be deleted from the source file and moved to different sheet or file.

Thanks,

 

[VaraK]

Hi Anirudh,

I hope you are doing well now. Try this code:

Sub InvldPhs()
Dim d As Double, g As Double, h As Double
Dim wb As String, std As String
Dim stdnum As String, phnum As String
Dim wbb As Workbook, wbk As Workbook

Set wbb = ThisWorkbook

On Error Resume Next
'change to your std codes workbook name
std = "STDCodes.xlsx"
'change this part to "E:\ExcelForum_Wo\" your drive and folder location where std codes file exists
wb = "E:\ExcelForum_Wo\" & std

Workbooks.Open (wb) ' Opens std codes file
' Gives you error message if the file is not present in your folder location
If Dir(wb, vbNormal) = "" Then
MsgBox "STD codes file not available. Please check the path"
Exit Sub
End If
Set wbk = Workbooks(std)
'change accordingly if your column is not column A to get the correct count
d = Application.WorksheetFunction.CountA(wbb.Sheets("Sheet1").Range("A:A"))
g = Application.WorksheetFunction.CountA(wbk.Sheets("Sheet1").Range("A:A"))
h = 2
'This does all your trick
For d = d To 2 Step -1


For g = 2 To g

stdnum = ""
If wbb.Sheets("Sheet1").Cells(d, 2) = wbk.Sheets("Sheet1").Cells(g, 1) Then
stdnum = wbk.Sheets("Sheet1").Cells(g, 2)
g = Application.WorksheetFunction.CountA(wbk.Sheets("Sheet1").Range("A:A"))
Exit For
End If

Next g

phnum = stdnum & Right(wbb.Sheets("Sheet1").Cells(d, 3), 11 - Len(stdnum))
wbb.Sheets("Sheet1").Cells(d, 4) = "'" & phnum
' I have used column D to paste the corrected phone number

If Len(phnum) <> 11 Then
wbb.Sheets("Sheet1").Rows(d).EntireRow.Copy wbb.Sheets("Sheet2").Cells(h, 1)
wbb.Sheets("Sheet1").Rows(d).EntireRow.Delete
h = h + 1
Else

End If
Next d
wbk.Close 'Closes std codes file now
End Sub

This should do the trick for you. Remember to first test this macro on a copy of your data.
Let me know incase you need any further help.

 

[VaraK]

Hightime, I should get a html maker now..code looks unformatted here.

 

[a4aniruddh]

Thanks a lot. It is really helpful.