cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements

Cisco Community Designated VIP Class of 2020

5174
Views
5
Helpful
7
Replies
Highlighted
Beginner

Keeping E164 numbers in the correct format in Excel spreadsheets

For years now I've struggled with keeping Telephony spreadsheets and BAT telephone number data exactly as it should be without being forced into a SUM or having the preceding+/00 omitted from the beginning of the number and I've finally figured out a way to keep the data in the correct format. Please forgive me if your an Excel whiz and this is nothing new or you even know of a better way to do this but this methods works beautifully.

(In my defence 13 years of rolling out Call Manager, almost all the peers ive come across experienced similar frustrations around this exact topic)

  1. Select the column you wish to keep in the E164/Number format
  2. Under the Home tab, select Format and open Format Cells.
  3. Choose a Custom Category
  4. Under Type, If your numbers start with the international code and no leading 0(s) then type the following: +# ?/? Hit Enter

For instance:

442077056000 will be converted to +442077056000

The need to keep this data in the correct format has become ever more important with the advent of the E164 dial plan. So helpful has this simple step been to my rollouts that I just had to share it. Ive attached a screenshot also. Good luck with your builds.

FormatCells.jpg

Everyone's tags (6)
7 REPLIES 7
Beginner

Keeping E164 numbers in the correct format in Excel spreadsheets

Incidentally,

I've chosen to post this in the forum in this way becuase so many Engineers search for answers here. If you can improve on this please, share your nuggets!

C

Beginner

I ran into this, the excel

I ran into this, the excel solution worked but when i BAT the file into CUCM, it complains about format of cell

+1XXXXXXXXXX Remote Destination must be a phone number or URI. interestingly if i configure a SNR manually the cucm allows the number to be put in E.164 format ie but BAT apparently complains about it. Has anyone run into this and any solution? i am running CUCM 9.1.2

Frequent Contributor

Does your BAT CSV file escape

Does your BAT CSV file escape the + sign?  Normally in CUCM your'e doing \+1XXXXXXXXXX .  BAT should let that through, though I'm not sure if RD's behave with it as I haven't personally tested on CUCM 9.1.2.

Hall of Fame Community Legend

Keeping E164 numbers in the correct format in Excel spreadsheets

Hi Craig,

Thanks for posting this up for others to share +5

This type of kind gesture is always appreciated!

Cheers!

Rob

"May your heart always be joyful
May your song always be sung" - Bob Dylan

Hall of Fame Master

Keeping E164 numbers in the correct format in Excel spreadsheets

I simply format the cell as Text and it works fine.

Chris

Beginner

Keeping E164 numbers in the correct format in Excel spreadsheets

Hey Chris,

      You're right!. You have to remember to change the format of the field before you insert the number. If your exporting data from say, Unity, the format will be general when you first open it and the data will be incorrect - in which case, applying the, '+# ?/?' Custom format will correct your data.

Cheers!

C

Frequent Contributor

It's funny that this is the

It's funny that this is the top hit for "E.164 numbers in Excel" for me, but this is much appreciated nonetheless.  Great info!

CreatePlease to create content
Content for Community-Ad
FusionCharts will render here