Find and replace telephone number and addresses

[guyzo]

Hi All. Once again, a bit of advice will be very much appreciated.

I have phone numbers in my database FM Pro 11 that are not entred in the correct format.

the numbers should be as follows:

(555) 123-4567 This is the exact format I require, including space and punctuation.

So, I have numebers like: 5551234567 or (555)1234567 and 555-123-4567 etc. I remember reading somwhere how to fix this so that they all read (555) 123-4567 But I just cant find the thread.

THEN, I need to be able to also export the telephone numbers to Excel, but the telephone number then has to read 5551234567 with no spaces or punctuation.

Secondly, I have all my street addresses in the format 123 Any Street, but id like to seperate the actual street number from the street name. I would need to create a field: Street Number, and Street Name to do this, I just need how to seperate the number from the street in an existing database.

Thanks so much in advance for your help.

[Lee Smith]

Hi Guy,

Parsing out phone numbers, addresses and other data after they have been entered as you've described, isn't as easy as you might think. Usually, it requires more work then meets the eye, because of the different variables that can be involved, you will need to understand how some of the Functions work such as the Filter, Left, Right, and Middle, etc., and things like the Let & Case Statements.

If all phone numbers contain 10 digits?

Filter(YourPhone; "0123456789") will return 5551234567

However, nothing involving parsing data after the fact is usually that simple.

Its been my experience that the phone numbers can contain 11, 7, or some other number of digits depending on whether or not there is an area code, leading digits or trailing digits (1 + area code, extensions, other countries, etc.)

to reformat your Ph #, you will need a Case or a Let Statement, something like

Let (

PH = Filter ( YourPH ; "0123456789" ) ;

"(" & Left ( PH ; 3 ) & ") " & Middle ( PH ; 4 ; 3 ) & "-" &

Right ( PH ; 4 )

)

are you ready to tackle your Address need?

HTH

Lee

[guyzo]

Hi Lee

As always, you have provided excellent feedback. All my numbers contain only 10 didgets (excluding spaces and brackets). As a newbie, I'm not sure where to run your instruction?

Filter(YourPhone; "0123456789 ") will return 5551234567 THEREFOR, if, after I figure out where to insert this command, I change the statement to Filter(YourPhone; "(012) 345-6789 ") it should change the contents of my phone field to (555) 123-4567?

Yes, Help with the address is very much required.

THANKS!!!

[Lee Smith]

Create a new calculation field, call it what you like, as an example, Filter Phone Number, and paste the Let Calculation into the calculation box. You will need to change "YourPH" in the Let to your current field that contains the phone number, and be sure to change result to Text.

Address are more difficult because of the variables. Lets get the Ph # figured out first.

BTW, I'm going to move this topic to Calculation Topic

PS

You need two new fields for this. One for the Filter that can be used for your export.

and the Let will be for viewing.

[Lee Smith]

Automatic message

This topic has been moved from "Database Schema & Business Logic → Finding & Searching" to "Database Schema & Business Logic → Calculation Engine (Define Fields)".

[Lee Smith]

Trying to figure out what calculation might help, I don't think we have enough information about your address field to give you an accurate answer. Unlike a phone numbers that has a pattern, i.e. a strict number of digits, one line addresses can contain all kinds of stuff such as apartment numbers, suite numbers, PO boxes, city, state, zip, etc.. I anticipate that you're going to want a field for each of these; street number, street address, city, state, and zip code.

In order to give you a set of calculations that can parse out what want, I think we need to evaluate what you have in that field currently. How about printing out as text, a sample of that field?

Lee

[Lee Smith]

Hi guy,

I want to point out a couple of typos I have in my calculations. In the filter function, the text should be “0123456789” instead of “0123456789x” note that I had a space before. The error in Let Calc was that I left off the trailing close “)” sorry if this is confusing to you.I have corrected both.

I made a demo of what I posted in hopes that you will see better when I'm trying to explain.

HTH

Lee

guyzoPH.fp7.zip

[guyzo]

Hi Lee

Thanks. I will be trying this out to see if I can make it work.

As far as the address field is concerned, it is very simply the Street address by itself including the street number. The city and zip code etc all have their own fields.

So, the strret field currently reads "123 Any Street" and i need to split it into "123" and "Any Street" as two seperate fields.

[comment]

If all your addresses follow the same pattern, you could use =

LeftWords ( Address ; 1 )

to get the leading number, and =

RightWords ( Address ; WordCount ( Address ) - 1 )

to get the rest.

Note that this will fail with an exception like:

123/a Any Street

[Lee Smith]

- snip - one line addresses can contain all kinds of stuff such as apartment numbers, suite numbers - snip -

As I stated in post 6, you need to be aware of some of the nuances that can effect the results of these functions. The attached file shows the two calcs provided by comment, and some modifications to the example address to illustrate what I mean.

Lee

guyzoAdd.fp7.zip

[guyzo]

Thanks so much. Will give it a try.

Have a great week.

[guyzo]

^{Hi Lee}

^{Thanks for the reminder. This woill work. But, perhapse a search script might be better as I an sent addresses reguralry to contact that are one field.}