a super reg expression?

[mikeo1313]

IMO this one is tough one.

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

To further complicate things 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 details to extract the 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 #.


Has anyone done a regular expression for access before that I can use to extract the phone & fax data to cust.phone & cust.fax columns with vba?

 

[Endre]

I am assuming that your descriptions and numbers are in the same field and that they are separated.
e.g:

Ph +228 897-223-8778
Fax 987 223 4567
Tel (023) 334 1234
Tl (023) 223-878-9998
Fx 123 (2324 9876)
Cell +14 223 123 23456


ANd that this is a once off data migration function - ie. you are upgrading an old set of data
There will need to be some (small) manual intervention as well and to minimise here is how I would do this :

Split / separate the decriptions from the numbers.
Categorise / group the descriptions to convert them.
Categorise / group the numbers and then convert them.

Using the Asc(String) you can find the first occurance of your phone / fax number in your field: (Yes I know I should not be posting code like this...)

Sub ConvertVals()
'====
'Create a recordset for your table
dim rst as recordset
Set rst = currentdb.openrecordset("Select PhoneDescAndNumberField from mytable;")
Do while not rst.eof
StrNo = ""
StrDesc = ""
i=1
Do until i > Len(rst!PhoneDescAndNumberField)
If Asc(Mid(rst!PhoneDescAndNumberField,i,1) >= 48 and Asc(Mid(rst!PhoneDescAndNumberField,i,1) <= 57
'Found a number
StrNo = StrNo & Mid(rst!PhoneDescAndNumberField,i,1)
If StrDesc = "" Then
StrDesc = Left(rst!PhoneDescAndNumberField,i-1)
end if
i= i + 1
Loop

'''Decide what to do with StrDesc and StrNo (see longer description below)
rst.edit
rst!newdesc = strdesc
rst!newNo = strno
rst.update
rst.movenext
Loop

end sub
'=====

I would create 2 more fields in your current table and update those values per record to StrDesc and StrNo. If your recordset above is an updateable one you can update it in the above code using rst.edit / rst.update

Next run a query on your table using only unique values of the newdesc to see the extent of your descriptive problems.

I would output the unique values to a new table with a second field that will either have "Tel", or "Fax" so you can link back to your original table and see which records are either Fax or Tel.

How you sort the numbers is another story as I don't know your numbering or examples. Maybe you can judge by the length of the number and place the appropriate +nnn, (nnn), or formatting +nnn (nnn) nnn-nnn-nnnn.....
If you look at the old text/number and the new one you might get an idea of what you want to do.

Hope this helps......