12 Replies Latest reply on Feb 22, 2017 7:30 AM by David Li

# format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

I have a file that has phone numbers in an excel document.  They are typed in as 8005551212, but the Excel formatting allows me to see as (800) 555-1212.  Even thought it's formatted like that in Excel, it comes over to Tableau as 8005551212 (in some instances, i notice 800555-1212, not sure why this is?).  But in any event, how do I format as (800) 555-1212?  Any thoughts?

• ###### 1. Re: format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

Hi Erin! I'm not sure why it comes in with the hyphen, but I would try a calculation like this:

"(" + LEFT(STR([Phone Number]), 3) + ") " + MID(STR([Phone Number]), 4, 3) + "-" + RIGHT(STR([Phone Number]), 4)

If [Phone Number] comes in as a string, you can take out all the STR() functions.

2 of 2 people found this helpful
• ###### 2. Re: format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

Formatting of numbers in Tableau is almost the same as in Excel:

Custom: type in the format you want to use. This format can be specified by an Excel style number code. See Number format codes in the Microsoft Knowledge Base for details.

(000) 000-0000

2 of 2 people found this helpful
• ###### 3. Re: format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

i notice 800555-1212, not sure why this is?

It is probably because it is typed or pasted as text. Depending on the task, this flexibility in Excel is either a strength or a weakness. As for formatting numbers in Excel or loading numbers into Tableau, this is a weakness, because all cells in the data column need to have the same data type in Tableau.

Here is a screenshot from LibreOffice Calc where I formatted the cells as (000) 000-0000. As we see, only the number was affected by the formatting code:

• ###### 4. Re: format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

That's a cool formatting trick, Kettan! I didn't realize that you could do that.

If the field formatting isn't uniform and Tableau ends up having to import it as a string, then I think my way will work. Otherwise, if they can all come in as integers, your way is much better!

• ###### 5. Re: format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

It made me happy to know that you liked it     The same is true for receiving tips such as this one from  Jonathan Drummey  today:  Re: How to filter and choose a part of data?

You are of course right that sometimes strings are better and at other times numbers. It all depends on what is needed. If Erin's users need to export data (from view data) as formatted, it must actually be a string, because currently view data doesn't support field formatting. But of course it could also be a number if those downloading the data don't mind formatting it in Excel themselves.

• ###### 6. Re: format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

Good info, I appreciate it!

Sent from my iPhone

• ###### 7. Re: format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

This calculation works just fine. "(" + LEFT(STR([Phone Number]), 3) + ") " + MID(STR([Phone Number]), 4, 3) + "-" + RIGHT(STR([Phone Number]), 4)

However some numbers in my data come across as 'Restricted', 'Unavailable' or 'Anonymous'. How can I set up the calculation so that is there is text then leave the text as it is?

• ###### 8. Re: format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

Hi Eric,

Your question is specific enough to your own case to probably warrant a new thread. However, here's my answer:

Setup your calculation to check for these non phone number entries like;

IF PhoneNumber = 'Restricted' or PhoneNumber='Unavailable' or ....... *setup the rest of your cases using or logic*

THEN PhoneNumber

ELSE *formula here to split out phone number*

END

1 of 1 people found this helpful
• ###### 9. Re: format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

Thanks Tom. I do not always know what the text will be in place of the phone number. How can I write this..

IF [Phone Number] is like char

THEN [Phone Number]

ELSE *Calculation*

END

• ###### 10. Re: format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

Hi Eric, I would do this:

IF ISNULL(INT(LEFT([Phone Number], 3))) THEN [Phone Number]

ELSE "(" + LEFT(STR([Phone Number]), 3) + ") " + MID(STR([Phone Number]), 4, 3) + "-" + RIGHT(STR([Phone Number]), 4)

END

Basically, this looks at the first 3 characters of [Phone Number], and if they can't be converted to integers, it leaves the string field as-is. Otherwise, it formats the number.

You can also remove the LEFT() function if you want, but if you don't have it and your data source includes some non-numeric text after the number (for instance, the # sign for an extension), the number won't get formatted.

1 of 1 people found this helpful
• ###### 11. Re: format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

Thanks David. This is what we were looking for.

• ###### 12. Re: format 10 digit integer to phone number (i.e. 8005551212 into (800)555-1212)

You're welcome!