Formating Telephone numbers

[#1]

Hi all. I got a problem. I'm new to Qlikview but I would like to know if there is some way to edit the number format when it has already been put into Qlikview as i do not have the raw data anymore. If so, please explain how but remember as I mentioned, I am new to Qlikview

Thanks so much

chris@obi.co.za

[#2]

[#3]

Hi

You have only 9 numbers, my code was for 10 numbers.

Try this code

='(' & left(PhoneNumber,2) & ') ' & right(left(PhoneNumber,4),2) & ' ' & right(left(PhoneNumber,6),2) & ' - ' & right(left(PhoneNumber,9),3)

It will give (12) 34 56 - 789

Anders

[#4]

that works but have you got a formula to change it from 123456789 to (012) 345-6789 maybe? Would be very helpful

[#5]

Hi

Try this

='(0' & left(PhoneNumber,2) & ') ' & right(left(PhoneNumber,5),3) & ' - ' & right(left(PhoneNumber,9),4)

Anders

[brenner_martina · Accepted solution]

Hi,

123456789 to (012) 345-6789

='(0'&Left(PhoneNumber,2)&') '&Mid(PhoneNumber,3,3)&'-'&
Right(PhoneNumber,Len(PhoneNumber)-5)

[#7]

Thanks for the Dimension but theres one problem. Some of the numbers are not filled in so they come out as (0) -. How do i change it to say either 'Incomplete' or to display nothing at all?

Sorry for asking so much

[#8]

You can check if it is 9 numbers.

=if(len(PhoneNumber) = 9,'(0' & left(PhoneNumber,2) & ') ' & right(left(PhoneNumber,5),3) & ' - ' & right(left(PhoneNumber,9),4),'Incomplete')

Anders

[#9]

thanks so much anders, it worked like a charm. I was wondering, can I do the same with a field if the value is 13 digits long and no spaces. so it appears as 1234567890123 for example?

[#10]

Change the 9 to 13

=if(len(PhoneNumber) = 13,...........

[brenner_martina]

Hi,

this expression is for every phonenumber larger then 5 digits!

=If(Len(PhoneNumber)>0,'(0'&Left(PhoneNumber,2)&') '&Mid(PhoneNumber,3,3)&'-'&
Right(PhoneNumber,Len(PhoneNumber)-5))