5 Replies Latest reply on Jul 22, 2014 11:00 AM by philmodjunk

Phone Number formatting Issue when trying to copy and paste

Title

Phone Number formatting Issue when trying to copy and paste

Post

Hello,

I have a data base that requires 3-4 different phone number fields and i have each field setup with a calculation so that it displays the phone number correctly when they input just the ten numbers....so 3334445555 appears as (333) 444-5555, using this calculation:

"(" & Left(Phone;3) & ") " & Middle(Phone;4;3) & "-" & Right(Phone;4)

My problem is at times they may want to copy one phone number field into another one, but since it is formatted already it copies like this:

(333) 444-5555 copied from one phone field looks like this in the new one
((33) 3) -5555

Is there a way to keep all the phone number fields with the formatting in place so that when they type in the ten digits it formats correctly AND if they copy an already formatted number into the phone field it saves it correctly?

• 1. Re: Phone Number formatting Issue when trying to copy and paste

Let(
Phone = Filter ( Phone ; 1234567890 ) ;
"(" & Left ( Phone ; 3 ) & ") " & Middle ( Phone ; 4 ; 3 ) & "-" & Right ( Phone ; 4 )
)

• 2. Re: Phone Number formatting Issue when trying to copy and paste

That works perfectly, thank you!

I do not really understand HOW that works, I just know that it does!

Thanks again!

• 3. Re: Phone Number formatting Issue when trying to copy and paste

The super talented, Mr Raybaudi's solutions does a few things:

The Let function  sets Phone to a variable  which is filtered using only the numbers 0-10.  So the first thing it does is remove everything that is not a number.   Then it puts that filtered variable back into the calculation with the parentheses and the - in the right spot.

The only problem you could potentially have is if someone types in a different amount of numbers then 10.

• 4. Re: Phone Number formatting Issue when trying to copy and paste

Thanks for explaining that Steve.

• 5. Re: Phone Number formatting Issue when trying to copy and paste

For a more sophisticated example of this, see: Formatting a text as phone number