Phone Numbers....

[macmangler]

Though not the biggest issue or really a problem at that, I am picking up some old database and the phone numbers are whacked out. So many people input data and there was no uniformity... so some records are...

(212) 123-4567

212 - 123 - 4567

212.123.4567

(212)1234567

Maybe if all were like.. "212-123-4567" and one forced "style"

I really don't want to break them up into 3 cells.... in some cases we have over sea phone numbers with + (44) 123-456789 coding...

Thoughts appreciated... but no need to lose sleep on this one.

[Genx]

Just Use:

//Define Unformatted Phone First:

Let(num = Filter( "0123456789" ; YourPhoneNumberField) ;

//Check If It the number is appropriate length

//If it is, format it appropriatley and remove colouring if it was previously invalid

If(Length(num) = 10 ;

TextColorRemove(

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

);

//If it's not color it red

TextColorAdd(num ; RGB (200;0;0))

)

)

//This will convert any number string of the proper length into the appropriate format

Put this calc in as an auto enter into your phone number field and uncheck do not replace existing values.

Then run a replace field contents over your number field to format the numbers appropriatley

~Genx

[macmangler]

Thanks!!!!

[Genx]

Ummm, re the international numbers you might want to add a check for a "-" tag at the begining of the number to let users enter it however they want for non-standard numbers e.g.

If(Left(YOURNUMBERFIELD = "-" ; 1) ; Right(YOURNUMBERFIELD ; Length(YOURNUMBERFIELD) - 1) ; calc above )

Just as a PS make sure you try the calcs first before replacing all your field contents... just to make sure i didn't forget a ";" here or a ")" there.

[hellohelp]

Thanks, this works great!

One other question with this. Say I want a field to be a lookup field for an existing phone number, but the user can edit it if need be. How can I have a field look up the existing phone number, but if the user edits the text, THEN have the calculation run?

Thanks,

Tom