How convert the value in single format

[Naps_230]

Hi,

i have data like different format, need to convert single format like below.

Condition: -

output should be in this format (xxx) xxx-xxxx .

if number contains 11 characters, then the 1st character should be removed and show the output in mentioned below format.

if number in different format, then align it with below format and achieve the output

number	Output
3862374878	(386) 237-4878
17347326127	(734) 732-6127
(616) 329-8098	(616) 329-8098

[Vegar]

To keep things streamlined without to many if-statemets and special handling I would try to do this for all rows.

1. clean the phone value from ), ( , blanks and other characters only keeping the number characters. Try using KeepChar(number,'1234567890')

2. Insert ( and ) into the right position in the new cleaned number string.

[lblumenfeld]

Are you trying to do this in the load script? That might be a good idea, so that you can create one field that has the phone number in the required format. For example,

I'm assuming that the only types of formats for number you will have are the ones you've listed. Is that correct? If so then the following should work.

If(Left(Number, 1) = '(',
Number, //it's the (xxx) xxx-xxxx format
If(Len(Number) = 11,
'(' & Mid(Number, 2, 3) & ') ' & Mid(Number(5, 3) & '-' & Right(Number, 3), // It's the 11 digit format
'(' & Mid(Number, 1, 3) & ') ' & Mid(Number(4, 3) & '-' & Right(Number, 3) // It's the 10 digit format
)
) as Output

This also assumes that there are no blanks to the left or right in Number. If there could be then you'd need to wrap each reference to Number in Trim(Number).

This formula will also work in a chart expression.

Hope this helps.