You'll have to know what you're doing, but here it is. You'll either have to redefine the column / row numbers to suit your spreadsheet or, move the data to those columns/ rows...so, columns 31-46 are the ones that contain all of my phone number fields and rows 3-975 are all the contact address's (one per line).
So, either you move your data into those columns/ rows, or make the changes in the VBA code below. The "colorindex=3" was to set the field to red when there was an anomoly in the field and it wasn't a clear cut solution. You will be alerted to numbers that are out of bounds with the code below. You'll see red colored fields where the VBA code burps. I had maybe 3-5 fields like that. My lead developer refined the code -
Sub reformatPhonNumbers()
Dim rowsOfNumber As Integer
Dim colsOfNum As Integer
Dim tempNum As String
For colsOfNum = 31 To 46
For rowsOfNumber = 3 To 975
tempNum = Cells(rowsOfNumber, colsOfNum)
If ((Len(tempNum) < 10) And (Len(tempNum) > 1)) Then
Cells(rowsOfNumber, colsOfNum).Interior.ColorIndex = 3
End If
If (Len(tempNum) > 9) Then
cachedNumber = ""
For i = 1 To Len(tempNum)
If (IsNumeric(Mid(tempNum, i, 1))) Then
If (Not ((Mid(tempNum, i, 1) = 1) And (Len(cachedNumber) = 0))) Then
If (tenCounter < 10) Then
cachedNumber = cachedNumber + Mid(tempNum, i, 1)
End If
End If
End If
Next i
'MsgBox cachedNumber
'fix the number to return
numberToReturn = ""
If (Len(cachedNumber) = 10) Then
numberToReturn = "1" + "-" + Mid(cachedNumber, 1, 3) + "-" + Mid(cachedNumber, 4, 3) + "-" + Mid(cachedNumber, 7, 4)
Cells(rowsOfNumber, colsOfNum) = numberToReturn
'MsgBox numberToReturn
End If
If (Len(cachedNumber) > 10) Then
numberToReturn = "1" + "-" + Mid(cachedNumber, 1, 3) + "-" + Mid(cachedNumber, 4, 3) + "-" + Mid(cachedNumber, 7, 4) + ";" + Mid(cachedNumber, 11, Len(cachedNumber))
Cells(rowsOfNumber, colsOfNum) = numberToReturn
'MsgBox numberToReturn
End If
End If
Next rowsOfNumber
Next colsOfNum
End Sub
Function getCleanNumber(strCell)
getCleanNumber = strCell
End Function