How to add a "1" to every number in my contact list

[TheMacs]

Have you tried that? I did days ago and it doesn't do what you think...my solution was simple and will work for every phone - few lines of VB in Excel, export from Outlook to Excel, run the code and export to Outlook. All fixed and 2500+ numbers fixed perfectly!

Slightly off topic, but did you really need VB code? I think we did this a while back with just a formula (concatenate maybe?).

 

[FBA]

It was a few things - but code was the fastest means to the end.

There were so many different types of entries in the number fields from decades of adding contacts - it was just easier to write some code to prune/ clean and add.

I had numbers with and without the "1", with and without the area code, with and without extensions, different modifiers for extensions, like "x" or "ext", or "p", or "w", none of which work with Android, which needs a semicolon.

I could have screwed around with the find and replace feature, or macros...but I used VB and the results were perfect for 1100 contacts with multiple numbers. I had perhaps 10-15 numbers that needed manual editing and that was all.

 

[TheMacs]

It was a few things - but code was the fastest means to the end.

There were so many different types of entries in the number fields from decades of adding contacts - it was just easier to write some code to prune/ clean and add.

I had numbers with and without the "1", with and without the area code, with and without extensions, different modifiers for extensions, like "x" or "ext", or "p", or "w", none of which work with Android, which needs a semicolon.

I could have screwed around with the find and replace feature, or macros...but I used VB and the results were perfect for 1100 contacts with multiple numbers. I had perhaps 10-15 numbers that needed manual editing and that was all.

Gotcha.

 

[JimSmith94]

It was a few things - but code was the fastest means to the end.

There were so many different types of entries in the number fields from decades of adding contacts - it was just easier to write some code to prune/ clean and add.

I had numbers with and without the "1", with and without the area code, with and without extensions, different modifiers for extensions, like "x" or "ext", or "p", or "w", none of which work with Android, which needs a semicolon.

I could have screwed around with the find and replace feature, or macros...but I used VB and the results were perfect for 1100 contacts with multiple numbers. I had perhaps 10-15 numbers that needed manual editing and that was all.

Could you post your VBA code please? A lot of my entries are messed up and I'd like to try your fix.

 

[FBA]

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

 

[JimSmith94]

Thank you sir, I'll give it a shot.

 

[b.mike]

Oh my goodness...it is amazing what computer savvy folks think is 'easy'

Posted via the Android Central App

 

[recDNA]

My home phone needs the one but my cell never does. Maybe a vzw thing?

 

[b.mike]

And a telus thing...I guess?

Posted via the Android Central App