The "no set format" will be a problem. Are the digits of the phone number the only numeric characters in the field? If so, we can use that fact to parse out and reformat the phone numbers fairly easily.
The phone numbers are a mixture of digits with dashes and some have parentheses around the area code. They were entered into a text field that allowed any format of text, the phone number is only in some of these records.I understand that the solution may not catch all of them because of the inconsistent data pattern. I'm hunting for good, not perfect.
Follow up - would this approach work?
Copy all the content from the text field containing phone numbers.
Strip out all punctuation including spaces but leave carriage returns.
Look for any character string of exactly 7 numbers. (USA phone numbers only)
Grab each group of 7 digits and paste into new field formatted as phone #.
Place old text field and new "possible" phone number on a layout enabling easy comparison of the two?
Is this a reasonable approach?
Could this be done in FileMaker and, if so, how?
You answer didn't really answer my question. I asked if the digits of the phone number are the only numeric characters in the field. The fact that other non numeric characters such as parenthesis and dashes are not at issue. If you have, say a phone number and a street number in the same field, that will keep the solution that I have in mind from working. But if the only numbers or those that are digits of the phone number, we can make it happen.
And what you are outlining is the solution that I had in mind.
Let ( [ P1 = Filter ( YourTable::YourField ; 1234567890 ) ;
Case ( Length ( P1 ) = 10 ; "(" & Left ( P1 ; 3 ) & ") " & Middle ( P1 ; 4 ; 3 ) & "-" & Right ( P1 ; 4 ) ;
Length ( P1 ) = 7 ; Left ( P1 ; 3 ) & "-" & Right ( P1 ; 4 ) ;
) // case
) // let
This can be a separate calculation field. Or you can use this with Replace Field Contents to "clean up" the existing field by stripping out the current text and replacing it with the formatted phone numbers. You can also set this up as an auto-entered calculation so that if you use Replace Field Contents to clean up your current data, the auto-enter calculation can ensure that new entries are automatically formatted.
Note: When using Replace Field Contents for the first time, it's a good idea to make a back up copy of your file first just in case you don't get the results that you expected.
You're right. I didn't answer your question. Sorry. I had tunnel vision at the time.
Phone numbers are not the only numbers in this text field. I also found numbers embedded in email addresses, street addresses and zip codes. As I said, this data was entered randomly.
But, I still think your approach could work - or help a lot.
It looks like your formula above is looking only at numbers and testing for two different lengths, 10 and 7, and formatting differently for each. 10 characters assumes an area code. 7 characters assumes no area code. Correct?
If I assume that street numbers and zip codes are never longer than 6 digits, won't that focus only on phone numbers?
I'm getting an error when I use the calculation from above.
FileMaker highlights the the first open parentheses after Case (see below) and shows a dialogue "A number, text, constant, field name or :(" is expected here.
Case ( Length ( P1 ) = 10 ; "(" & Left ( P1 ; 3 ) & ") " & Middle ( P1 ; 4 ; 3 ) & "-" & Right ( P1 ; 4 )
Also, should there be a closing square bracket somewhere?
Apologies. I adapted what I posted from a more detailed version that handles numbers with international calling prefix digits and that version needed square brackets to enclose a list of entries that assigned values to "let" variables. The left square bracket should be removed.
You are correct in how this is intended to work, but if there are any numeric digits present in this field that are not part of the phone number, this method will not work as those other digits will be included as part of the number.
There may be ways to deal with that, but without seeing some examples of how this data looks, I can't suggest anything with confidence that it will work for you.
Thanks. I tried using a calc field just to see what I'd get.
As you said, if any numbers are in the source field, they are pulled into the new calc field. Looking at the results, numbers can be:
- embedded in email addresses. (most)
- street addresses
- zip codes
- company names.
Extracting the email addresses from the source field would help a lot. Is there a calc to do that?
I believe you already posted about this issue in another thread and I've posted an answer to it. Let me know if that was someone else. (Didn't note the name at the time.)
Yes. That was probably me.
Emails with numbers in them were complicating the extraction of phone numbers.
So, after extracting the email addresses, I'll delete them from the source field and eliminate at least that one source of problem.
If I could see a sample of this data (with sensitive data replaced by placeholders), I might be able to suggest a better alternative as removing the email address still leave other items that need to be dealt with.