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!

 

[Joe4]

Is there just one entry per cell, or multiple entries per cell?

Since there are a whole bunch of different structures being used, I am fairly certain that these are text entries, and not numeric ones. The tricky part about that is that Formatting only works on numeric entries, not text ones. So I don't think we will be able to use formatting to solve your problem. It will probably require either a fairly complex formula (to account for all the different options), or VBA (my preferred solution). The advantage to VBA is that you can overwrite your current entry without having to use extra columns.

Is VBA a viable solution for you?

 

[AwfulSpider]

Is there just one entry per cell, or multiple entries per cell?

Since there are a whole bunch of different structures being used, I am fairly certain that these are text entries, and not numeric ones. The tricky part about that is that Formatting only works on numeric entries, not text ones. So I don't think we will be able to use formatting to solve your problem. It will probably require either a fairly complex formula (to account for all the different options), or VBA (my preferred solution). The advantage to VBA is that you can overwrite your current entry without having to use extra columns.

Is VBA a viable solution for you?

Just one entry per cell.

I'm not super confident with VBA, I've made a few simple macros, but I was expecting that to be the best solution.

So VBA is a viable solution if you can provide a lot of help lol

Thanks!

 

[Joe4]

Do you have any entries that already have the "1" at the beginning in their initially?
Regardless of all the extra characters the entries have, will they all have exactly 9 numbers in them (like the following examples you gave do):

(555)5555555, 555-555-5555, 5555555555

 

[AwfulSpider]

almost never. As in there are going to be some cells that have a note like "this person should be called on a tuesday" because getting people to be consistent and follow instructions is so simple, lol

99% of the entries will be 10 digits with different formats (I checked, you are correct they're all "general" formatted, not numeric). So as long as it won't crash the code, I'm fine picking out the handful of random errors, with a 1 or an extra note.

 

[AwfulSpider]

almost never. As in there are going to be some cells that have a note like "this person should be called on a tuesday" because getting people to be consistent and follow instructions is so simple, lol

99% of the entries will be 10 digits with different formats (I checked, you are correct they're all "general" formatted, not numeric). So as long as it won't crash the code, I'm fine picking out the handful of random errors, with a 1 or an extra note.

sorry, 9 numbers, not 10

 

[Candyman8019]

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.

 

[Rick Rothstein]

sorry, 9 numbers, not 10

Why isn't it 10 digits (123-456-7890) ?

 

[Joe4]

almost never. As in there are going to be some cells that have a note like "this person should be called on a tuesday" because getting people to be consistent and follow instructions is so simple, lol

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)?

 

[AwfulSpider]

Why isn't 10 numbers (123-456-7890)?

because I haven't finished my coffee...