All UK mobile numbers are as follows:-

XXXXX XXX XXX (11 digits)

e.g 07747 340 147 (11 digits)

Can someone help me so that when i put in XXXXXXXXXX this gives me a result of (XXXXX) XXX XXX or XXXXX XXX XXX

Thanks in advance.

Hi akfruit!

To get (XXXXX)XXX XXX

"(" & Left( Abs(Phone Number Field), 5) & ")" & Middle( Abs(Phone Number Field), 6, 3) & " " & Right( Abs(Phone Number Field), 3)

To get XXXXX XXX XXX

Left( Abs(Phone Number Field), 5) & " " & Middle( Abs(Phone Number Field), 6, 3) & " " & Right( Abs(Phone Number Field), 3)

HTH,

Kate :P

Kate

When I try both of these it leaves off the zero (eg 07737) and doesn't put in the correct telephone number.

Any idea why this is?

Thanks.

Martin

Martin,

Alright this is where an FM God I am not! I know I had this problem before with a leading "0" and I think I figured it out, but I'm drawing a blank this morning. I'll keep trying, but does anyone else know why this happens?

Kate :P

Lets establish one fact the resulting format are bound to be "text" to behave most predicatble!

My stab at it would be this:

"(" & Replace ( Replace ( Left ( entry ; 11 ) ; 9 ; 0 ; " " ) ; 6 ; 0 ; ") " )

...and please note that last paranthesis if followed by a inside the quotes!

--sd

Edited May 16, 200619 yr by Guest

Excellent that works treat.

Thank you

As usual...I'm impressed and still have sooooo much to learn!

Kate :P

Just to clarify: Abs() is a numerical operation, so its result will always be a number, with no leading zeros.

Abs ( "05" )

returns 5.

Use the Filter() function to strip any non-numerical characters from the input.

Just to clarify: Abs() is a numerical operation

Thanks for ironing out my convoluted expression! Indeed it should be said.

--sd

Hello Folks

Further to my post yesterday to say that it works. I have encoutered a little problem.

When I open a new record Iget "()" automatically showing in the field.

Anybody know why this is happening?

Thanks

Martin

Yeh add an if expression in there to check if the length is correct, otherwise it tries to format a non existant number.

Yes the checkbox in lower left corner have been deselected... It makes most sense to have it checked to deal with NULL values!

--sd

ooooh, right, forgot bout that :P