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]

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

Well, you have a non-VBA and a VBA solution now.

The advantage to non-VBA is that it is not VBA (so you don't need to enable VBA/Macros, or anything like that).

The advantage to VBA is that you can do it without extra columns, and you can run it with a single click of a button.

 

[smalik]

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.

Just curious how is the above formula any different than a simple concatenation
"+1"&C27
Where Cell C27 has the original 10 digit phone number

 

[Joe4]

Just curious how is the above formula any different than a simple concatenation
"+1"&C27
Where Cell C27 has the original 10 digit phone number

Go back and read post #1. The entries aren't always exactly 10 numbers. There may be other characters as well.

I just discovered something else. The formula version does not handle blank or text entries well. It returns "+1" in those cells.
The VBA solution I provided handles that. It will only change it if it finds exactly 10 numbers in the cell. Otherwise, it will leave the cell "as-is".
Just something else to keep in mind.

 

[AwfulSpider]

Go back and read post #1. The entries aren't always exactly 10 numbers. There may be other characters as well.

I just discovered something else. The formula version does not handle blank or text entries well. It returns "+1" in those cells.
The VBA solution I provided handles that. It will only change it if it finds exactly 10 numbers in the cell. Otherwise, it will leave the cell "as-is".
Just something else to keep in mind.

Yes, I've noticed this, and am using a work around right now. The VBA version is definitely more elegant. I'm going to have to pass whatever solution I use to others though so I'm less confident about getting to use the VBA solution.
Thank you though!

 

[Joe4]

I'm going to have to pass whatever solution I use to others though so I'm less confident about getting to use the VBA solution.

If you are running on new data files that come in, you can have them save the VBA code to a Personal Macro Workbook. This is VBA code that is available to them whenever they start up Excel on their computer. So then this VBA code would be available to them from EVERY Excel file they open.

Then, there are a few different ways they can run it.
- From the Macro menu
- Assign a keyboard shortcut to it, and use that

I have a special formatting macro that I use on a ton of files, and I use the later option on it. Once set-up, it makes things very quick and easy to run.

 

[Candyman8019]

If you go the formula route, you can also check to see if the length of the result is at least 1 digit...if it is not then return {blank}. For example:
=IF(LEN(TEXTJOIN("",TRUE,IFERROR(MID(c27,ROW(INDIRECT("1:50")),1)+0,"")))<1,"","+1"&TEXTJOIN("",TRUE,IFERROR(MID(c27,ROW(INDIRECT("1:50")),1)+0,"")))