I need help writing a script (if possible) to convert telephone numbers. I often import data and number may come in as:

2128542583

(212) 854-2583 etc.

I want script to change to 212-854-2583

I'm willing to edit script as necessary depending on what format I'm importing. Since this same phone field may contain international, etc. I would rather handle with script than calc field:

find all entries with xxxxxxxxxx (10 characters) and replace with xxx-xxx-xxxx for example.

Hope this makes sense.

I scarfed this auto enter calculation replaces existing value calc from somewhere. You can modify to your needs.

// Make phone number into "XXX-XXX-XXXX"

Let(

CONTACT_PHONE_NUMBER = Filter(CONTACT_PHONE_NUMBER;"0123456789");

Left(CONTACT_PHONE_NUMBER;3) & "-" & Middle(CONTACT_PHONE_NUMBER;4;3) & "-" & Middle(CONTACT_PHONE_NUMBER;7;4))

I think it's prettier to add parentheses around the area code, so in this case the second line above would be

"(" & Left(CONTACT_PHONE_NUMBER;3) & ") " & Middle(CONTACT_PHONE_NUMBER;4;3) & "-" & Middle(CONTACT_PHONE_NUMBER;7;4))

I tried to use this and it didn't work. How exactly do you get this work? I am a rookie.

Do you use two fields for each one?

Home phone, Homephone2(with calc in it)

Work Phone, Workphone2(with calc in it)

If that is the case, the user enters the number in the first field, and what I want displayed shows in the other field.

I have 3 phone fields and it would be great if when they entered the number, once they tabbed out of it, it changed into the correct format.

Thanks Dave

Thanks. However I don't want to use Calc field, is this possible with a script?

Hi David,

Here is a file I made up for you that shows how to script the Format. Because this was done on a Mac and you are on a Windows, it may be necessary for you to Enlarge the Fields to see some of the data.

HTH

Lee

ScriptedPhoneFormat.fp5.zip

Thanks Lee!

Howevever, I don't fully understand the g_raw_temp field. What is it doing? I can't quite get it to work (sorry-novice)

In 7, it can be an auto-entered text field with the calculation above and 'Do not replace existing value' deselected. It will update whenever the field is modified and doesn't require an additional calculation field.

Hi David,

I didn't pick up on the fact that you were using v7, for some reason, until -queue- responded.

I could send you an explanation of what the global field "g_Raw_Temp" is there for, but if I were you, I would take advantage of any and all v7's new features, and follow -queue-'s advice.

Lee

Lee,

Yes, please provide more info. The auto enter will work fine for future entries, but I still need to clean up what's in DB.

Thanks,

David

Also, does not appear that the auto enter will work for international numbers as these are cut off to 10 digit numbers.

Take a look at Business Tracker. It has a telephone number autoenter calculation that works with internation numbers. It has a number of different formats based on the country.

I sent the information to you in a Private Message.