Need Help formatting phone number using xmpie

[Kimba]

Need help to program the "( )" around are code in phone numbers using XMpie. I was trying to do this using the substring in Q-lingo but kept making the numbers disappear. Any help would be great
thanks

 

[Bo3b]

several problems in XMPie when attaching to database (csv) files - if it "sees" the number as a number it will drop the leading zero.
I usually create a 'dummy' first record with text everywhere where I want text to be recognised, XMPie will then treat all fields in that position as text.

Problem then is using FormatNumber XMPie will drop the leading zero , as it now converts the text to a number. So you have to use substrings…
if in your database your phone number (field name phone) was 057657654 and you want this to be formatted as (05) 765 7654 then your qLingo would be "(" & SubString(|->[phone], 0, 2) & ") " & SubString(|->[phone], 2, 3) & " " & SubString(|->[phone], 5, 4)

which translates to put in a ( then starting at position 0 of the field 'phone' take 2 characters, then a ) and a space then from position 2 take the next 3 characters then a space then from position 5 take the next 4 characters
0 is the first character of the string

make sense?