phone number typed in comments, help me get it out 1

[nickrugado]

If I have a phone number in a comments field with words before, before and after or after with any of the formatting then I want to pull that data out and put it in a field called phone number.

An update query or vb behind a form would be cool too.


phone numbers could be formatted...
415.555.1212
415 555 1212
415-555-1212
4155551212

Anything in the expression builder that pulls this out??

Thanks,
Nick

 

[dhookom]

You could create a new function in a module and use it in your query like:

  UPDATE tblNoNameGiven
  SET [Phone Number] = PullPhone([Comments])
  WHERE Len([Comments] & "") >=10

Function PullPhone(strText As String) As Variant
    Dim intChar As Integer
    Dim strTextClean As String
    Dim strExamine As String
    PullPhone = Null
    'first get rid of all periods, hyphens, and spaces
    strTextClean = Replace(Replace(Replace(strText, ".", ""), "-", ""), " ", "")
    'Then loop through the string to find 10 numbers in a row
    If Len(strText) < 10 Then 'no phone number
        PullPhone = Null
     Else
        For intChar = 1 To Len(strTextClean) - 9
            strExamine = Mid(strTextClean, intChar, 10)
            If IsNumeric(strExamine) Then 'found phone number
                PullPhone = strExamine
                Exit For
            End If
        Next
    End If
End Function

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

that's some impressive code Duane! I was still mulling how it MIGHT be done and you've got it coded and ready to go!

Leslie

 

[dhookom]

Thanks Leslie. I have coded through tons of crap data in the past.

I thought about adding the removal of ()s and formatting the output to look more like a phone number but expect Nick can make the changes if he needs them. If not, he can reply back.

Duane
Hook'D on Access
MS Access MVP

 

[nickrugado]

It is awesome and I'm learning even more VB by reading through it.

I have one question, what does it do if it finds two strings of numbers that are in the comments? (not in orig scope of question) Would it error out or update the same field twice?

 

[nickrugado]

Oh, I do strip down the phone numbers to bare numbers stored in a text field and then format it later. I get so many different incomming fomats from other sources it's easier to work with like that.

 

[dhookom]

what does it do if it finds two strings of numbers that are in the comments

Test it ;-)

  SELECT  PullPhone([Comments]) as PhoneNum, [Comments]
  FROM tblNoNameGiven
  WHERE Len([Comments] & "") >=10

Duane
Hook'D on Access
MS Access MVP