Format phone number in a report

[christinequick]

I'd like to have a user's phone number format as (xxx) xxx-xxxx in a report.

I tried the suggested text mode below, but it returns only 7 out of 10 numbers. it's missing the middle three numbers.

valueformat=HTML
textmode=true
valuefield=phoneNumber
valueexpression=IF(ISBLANK({phoneNumber}),"",CONCAT("(",LEFT({phoneNumber},3),") ",MID({phoneNumber},4,3),"-",RIGHT({phoneNumber},4)))

Any ideas what I'm doing wrong?

[AliPen · Accepted solution]

My guess is the MID function is not available. I replaced it with this:

LEFT(RIGHT({phoneNumber},7),3)

 This pulls the right 7 digits (removing the area code) and then grabs the left 3 digits of that result. This worked for me in a quick test. 

Full expression: 

valueexpression=IF(ISBLANK({phoneNumber}),"",CONCAT("(",LEFT({phoneNumber},3),") ",LEFT(RIGHT({phoneNumber},7),3),"-",RIGHT({phoneNumber},4)))

[christinequick]

That worked perfectly! Thank you!

[christinequick]

WF Support also provided this option using SUBSTR which works as well:

displayname=Phone Number
valueexpression=IF(ISBLANK({phoneNumber}),"",CONCAT("(",LEFT({phoneNumber},3),") ",SUBSTR({phoneNumber},3,6),"-",RIGHT({phoneNumber},4)))
valuefield=phoneNumber
valueformat=HTML