Weird Phone number format question

[AwfulSpider]

So I get lists of phone numbers that I need to format in a weird way for batch uploads to our system, like this: +15555555555.

I get the numbers in a column in excel all kinds of ways, (555)5555555, 555-555-5555, 5555555555, you get the idea.
So what I’ve been doing is copy pasting ‘+1 (to keep the + sign) and deleting any extra bits. But these lists are hundreds of numbers long, so any formatting tricks would help greatly.
Thanks in advance!

 

[AwfulSpider]

I am a little confused now. Are you saying that these cells do not just contain the phone number, but may also contain other text too?
Can you please post a sampling of actual data you have in there (you can change all the phone numbers to 5s so you aren't posting any real phone numbers)?

Perm_Cell_Pager
555-555-5555
555-555-5555
5555555555​
same
555-555-5555
5555555555​

 

[AwfulSpider]

Give this formula a try...it will strip non-numeric characters and add a +1 to the front.
="+1"&TEXTJOIN("",TRUE,IFERROR(MID(C27,ROW(INDIRECT("1:15")),1)+0,""))

NOTE: This is an array formula so use CTRL+SHIFT+ENTER rather than just enter.

I'm getting a Compile error: syntax error.

remember, I'm super new with VBA...

 

[Candyman8019]

That is just a regular formula...not VBA

 

[AwfulSpider]

That is just a regular formula...not VBA

Oh, thanks! How do I get it to reference the column with data?

 

[Candyman8019]

Change C27 to the cell that contains your text. Then copy the formula down to each row in the column.

 

[Candyman8019]

Also, depending on the length of text that may be in your column, you may need to change the indirect from 1:15 to 1:80 for example to handle more text.

 

[AwfulSpider]

Also, depending on the length of text that may be in your column, you may need to change the indirect from 1:15 to 1:80 for example to handle more text.

you are brilliant and I owe you so much of my time.

Thank you!

 

[AwfulSpider]

I am a little confused now. Are you saying that these cells do not just contain the phone number, but may also contain other text too?
Can you please post a sampling of actual data you have in there (you can change all the phone numbers to 5s so you aren't posting any real phone numbers)?

Thank you for all your help, I have a code that's working now.

 

[Joe4]

Here is your VBA solution:

Sub MyFixPhoneNumbers()

    Dim c As String
    Dim r As Long
    Dim lr As Long
    Dim temp As Variant
    
    Application.ScreenUpdating = False
    
'   Set which column to run this on
    c = "A"
    
'   Find last row with data in column above
    lr = Cells(Rows.Count, c).End(xlUp).Row
    
'   Loop through all rows, starting in row 2
    For r = 2 To lr
        temp = NumericOnly(Cells(r, c))
        If Len(temp) = 10 Then
            Cells(r, c).NumberFormat = "@"
            Cells(r, c) = "+1" & temp
        End If
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!", vbOKOnly
    
End Sub


Function NumericOnly(mystr As Variant)

    Dim myOutput As String, i As Integer

    For i = 1 To Len(mystr)
        If IsNumeric(Mid(mystr, i, 1)) Then
            myOutput = myOutput & Mid(mystr, i, 1)
        End If
    Next i

    If Len(myOutput) > 0 Then NumericOnly = myOutput * 1

End Function

It will fix it right in place, without needing any extra columns.
Simply run the "MyFixPhoneNumbers" macro/procedure.

The only thing you may need to change in the code is the column to apply it to, and the row number to start on.
Those should be quick and easy changes.

 

[AwfulSpider]

Here is your VBA solution:

Sub MyFixPhoneNumbers()

    Dim c As String
    Dim r As Long
    Dim lr As Long
    Dim temp As Variant
   
    Application.ScreenUpdating = False
   
'   Set which column to run this on
    c = "A"
   
'   Find last row with data in column above
    lr = Cells(Rows.Count, c).End(xlUp).Row
   
'   Loop through all rows, starting in row 2
    For r = 2 To lr
        temp = NumericOnly(Cells(r, c))
        If Len(temp) = 10 Then
            Cells(r, c).NumberFormat = "@"
            Cells(r, c) = "+1" & temp
        End If
    Next r
   
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!", vbOKOnly
   
End Sub


Function NumericOnly(mystr As Variant)

    Dim myOutput As String, i As Integer

    For i = 1 To Len(mystr)
        If IsNumeric(Mid(mystr, i, 1)) Then
            myOutput = myOutput & Mid(mystr, i, 1)
        End If
    Next i

    If Len(myOutput) > 0 Then NumericOnly = myOutput * 1

End Function

It will fix it right in place, without needing any extra columns.
Simply run the "MyFixPhoneNumbers" macro/procedure.

The only thing you may need to change in the code is the column to apply it to, and the row number to start on.
Those should be quick and easy changes.

Thank you so much, I will save this for any potential issues down the road. Your time is very much appreciated!