access vba extract phone & fax in address column of table

Question

I have a table with phone & fax data in an "address" column that I want to put into their separate "phone" and "fax" column.

The phone comes in a various forms: phone, T , ph, tel., tels., fono. The same issue occurs with fax, i.e. Fax & F.

Ideally, I think the following is a good description of the data.

1. Not all numbers pertain to phones (i.e. zipcodes) not all phone numbers are preceeded by anything that identifies it (ie. phone, ph, tel.).

2. Their are atleast 10+ consecutive numbers in a phone (with international codes & dialing can be 13-15 numbers) which might have a few of any of the following separators [.],[-],[)],[(] or A single space[ ].

3. The first occurance of the strand of numbers as described in #2 is the phone #, the second occurance is the fax #.

4. If their isn't a fax # found, (a 2nd occurance of #'s) I'd like the 1st occurance copied to the 2nd , if available.

Answer 1

Since there's no standardized way of representing the data I think you're going to have a lot of trouble writing a completely automated VBA function that gives you good results. If it were me I'd paste the address column into Excel and use filters, LEFT, MID, RIGHT, INSTR, SUBSTITUTE, etc. formulas, and regexps to process the data by hand.