Phone Number formatting

[#1]

Hello!

It seems to be simple issue, but I was not able to find the answer myself, so I would need an expert advice from the forum members:

1. I have a US phone numbers field in the Table Box where the data is stored in simple numeric format: 2223334444

2. I would need it to be presented in the standard way: (222) 333-4444 or +1 (222) 333-4444, but I was not able to find the proper format in the "Number" tab of the TB Properties.

I would appreciate an advice.

Regards,

VK

[rwunderlich · Accepted solution]

You cannot do that kind of formatting in a TableBox. You have to do the formatting in either the script or a Straight Table chart using an expression like:

'(' & left(RawPhone,3) & ')' & mid(RawPhone,4,3) & '-' & mid(RawPhone, 7)

See the attached for an example of both methods.

-Rob

[Miguel_Angel_Baeyens]

Unless someone provides new and more useful information about this, I'm afraid you should create your own format with string functions like

'+1 (' & Left(PhoneField, 3) & ') ' & Mid(PhoneField, 4, 3) & '-' & Right(PhoneField, 4) AS PhoneField

in your load script or something similar anywhere in your graphs, as a phone number is not really a number (you don't add or substract one from another)

[#4]

Thank you, Rob and Miguel, it works perfectly!

Regards,

VK

[swathid1]

Hi,

I am new to Qlik and finding it hard to follow formatting and unable to access PhoneFormat.qvw from Qlik Online. Could you please explain. 

My data has phone #as follow:

030-0074321

(5) 555-4729

(171) 555-7788

0921-12 34 65

88.60.15.31

0621-08460 

....

Regards,

Swathi

[BrunPierre]

@swathid1 What's your expected output?

[HirisH_V7]

Hi , This may result in only numbers. Instead if you're looking certain output, do mention same.

KeepChar(PhoneNumber, '0123456789') AS PhNO_NumericOnlyField

[swathid1]

Thank you Hirish. This is what i used. Anyway i am suck with Date formatting now.

my column has multiple formats, i would like to keep them in DD/MMM/YYYY Australian format: 

Order Date

39451

39203

2008-11-02

02/04/2008

03/10/2008

24/11/2008

04/24/2008

04/10/2008

05/12/2008

05/12/2009

05/09/2009

05/01/2009

05/02/2008

05/04/2008

05/08/2008

05/08/2007

05/10/2008

Much appreciate any help regarding this.

[HirisH_V7]

You can use something like this, by including your more date formats.

Date(
Alt(
Date#([Order Date],'YYYY-MM-DD'),
Date#([Order Date],'DD/MM/YYYY')
)
,'DD/MM/YYYY') as [Final Order Date]