Extract telephone numbers from long summary (1 Viewer)

jamaicamonn · May 11, 2017

Hi All,

I am trying to extra telephone numbers from several business reports. The narrative is usually type as follows:

Example 1: 2016/12/31 20:10 Doe, John Desk105 front , 57yo male interested in purchasing cyber software for business

2016/12/31 20:11 Doe, John Desk105 front, call back to complete sale

#123-123-1234"

Example 2:2016/12/31 20:10 Doe, John Desk105 front , 57yo male call from 123-123-1234 interested in purchasing cyber software for business

2016/12/31 20:11 Doe, John Desk105 front, call back to complete sale

Sometimes the telephone number can be isolated or mixed in with the text (example 2), but usually it is always divided with a dash in this format "123-123-1234."

Any ideas?

isladogs · May 11, 2017

Assuming the text string is in a field called strText, then this should do it....

Code:

txtPhone=Mid(strText,InStr(strText,"-")-3,12)

However, if you have any international phone numbers, it won't work:
a) if the number of digits in the phone number isn't 12 (including the '-')
b) with a different phone format e.g. +44 1962 670234

jamaicamonn · May 11, 2017

Hi ridders,

The field name is “comments” and the data type is a memo. If I change the data type to text it cuts some of the text out.

When I click design a query, what exactly should I type inside the field after I add the table:

[comments]txtPhone=Mid(strText,InStr(strText,"-")-3,12)

Note: I am an access beginner.

jamaicamonn · May 11, 2017

ridders said:
Assuming the text string is in a field called strText, then this should do it....

Code:

txtPhone=Mid(strText,InStr(strText,"-")-3,12)

However, if you have any international phone numbers, it won't work:
a) if the number of digits in the phone number isn't 12 (including the '-')
b) with a different phone format e.g. +44 1962 670234

Hi again,
So I figured out I could use this:
txtPhone: Mid([comments],InStr([comments],"-")-3,12)

However, some employees type their names as Doe-John, and it pulls that. So I need the query to grab a selection/str of text in the format of "***-***-****"
Thank you

isladogs · May 11, 2017

That makes it slightly more complicated.
I also have a feeling that there will be other complications once I've dealt with that

To save time, please can you export a table containing the Comments field to an Excel spreadsheet & upload it. As you have less than 10 posts, you will need to zip it.

isladogs · May 11, 2017

Hi again

I carried on with this anyway.
The following routine will work with the examples you've given me so far.
It will still fail if there are other complications ....

This routine saves the phone number into a TEXT field called 'Phone'.
If you don't have that, you'll need to create it - field length = 12

Open the Visual Basic Editor then paste the code below into a new module.
Two items are shown in RED
Replace the table name 'Table1' with your own table name.
Or if you have a query linking several tables, use the query name instead

If you already have a field for the phone number called something else, modify that as well

Code:

Option Compare Database
Option Explicit

Public Function GetPhoneFromComments()

    Dim rst As DAO.Recordset
    Dim strText As String, strPhone As String
 
    Set rst = CurrentDb.OpenRecordset("[COLOR="Red"]Table1[/COLOR]", dbOpenDynaset, dbSeeChanges)

    With rst
        .MoveFirst
        
        If Nz(!Comments, "") <> "" Then
           Do Until .EOF
                .Edit
                strText = Mid(!Comments, InStr(!Comments, "-") - 3)
                strPhone = Left(strText, 12)
       
                If Mid(strPhone, 4, 1) <> "-" Or Mid(strPhone, 8, 1) <> "-" Then     
                    strText = Mid(strText, 5)
                    strPhone = Mid(strText, InStr(strText, "-") - 3, 12)
                End If
            
                ![COLOR="Red"]Phone[/COLOR] = strPhone
                .Update
           .MoveNext
           Loop
        End If
    End With
        
    Set rst = Nothing

End Function

Still in the VBE, press ctrl+G to open the Immediate Window
In that window, type GetPhoneFromComments

This will populate the Phone field for all your records
If this doesn't fix it, upload data as requested in a spreadsheet OR zip your database & upload it

jamaicamonn · May 24, 2017

Hi Ridders, your last query expression worked for 600 comments but when I tried the VBE code I received an error at line " strText = Mid(!Comments, InStr(!Comments, "-") - 3)" because I assume it is trying to grab a number between spaces, but one of our sales reps typed the number "123-123-12342012".

There must be a away to set it using an IF statement right? Even if its null I can go back later and manually enter it.

isladogs · May 24, 2017

jamaicamonn said:
Hi Ridders, your last query expression worked for 600 comments but when I tried the VBE code I received an error at line " strText = Mid(!Comments, InStr(!Comments, "-") - 3)" because I assume it is trying to grab a number between spaces, but one of our sales reps typed the number "123-123-12342012".

There must be a away to set it using an IF statement right? Even if its null I can go back later and manually enter it.

Not sure what your last paragraph is saying.

As I said before, the code will only work if the phone number has 12 digits in the format you provided. The new example has 16 digits and is clearly an incorrect number. How would any code know which digits to use?
It seems likely there will be other exceptions or errors

I could write code to trap errors but it would save a lot of time if you upload the data (or realistic alternative data) which includes all such variations

Extract telephone numbers from long summary (1 Viewer)

jamaicamonn

New member

isladogs

MVP / VIP

jamaicamonn

New member

jamaicamonn

New member

isladogs

MVP / VIP

isladogs

MVP / VIP

jamaicamonn

New member

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread