Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Cleaning up OCR'd addresses with Search Function (or VBA)

Latest post Thu, May 15 2008 3:46 AM by Bob Phillips. 1 replies.
  • Wed, May 14 2008 7:32 PM

    • veradusit
    • Top 75 Contributor
    • Joined on Mon, Mar 17 2008
    • Posts 4
    • Points 99

    Cleaning up OCR'd addresses with Search Function (or VBA)

    I have a spreadsheet of addresses that was scanned using OCR.  The scanner did an acceptable job of text recognition, however due to additional Title's (i.e. MD, PhD, RN, etc) the columns in excel became wildly unmanageable.  I need to extract Telephone, Fax and E-mail from a long list of contacts, however, those values are dispersed over many columns.  The good news is that in front of all emails is the word "E-mail:" and in front of all Faxes is the word "Fax:"  A four person sample sheet is attached.

     

    Thanks

     

     

    • Post Points: 21
  • Thu, May 15 2008 3:46 AM In reply to

    Re: Cleaning up OCR'd addresses with Search Function (or VBA)

    Here is a very simple, basic UDF

    Function ContactDetails(rng As Range)
    Dim aryDetails(1 To 3) As String
    Dim cell As Range

        Set cell = rng.Find("e-mail")
        If Not cell Is Nothing Then
       
            aryDetails(1) = LTrim$(Replace(cell.Value, "E-mail:", ""))
        End If
       
        Set cell = rng.Find("telephone")
        If Not cell Is Nothing Then
       
            aryDetails(2) = LTrim$(Replace(cell.Value, "Telephone:", ""))
        End If
       
        Set cell = rng.Find("fax")
        If Not cell Is Nothing Then
       
            aryDetails(3) = LTrim$(Replace(cell.Value, "Fax:", ""))
        End If
       
        ContactDetails = aryDetails
    End Function

     

    To use it select all three cells, say M2:O2, and enter =ContactDetails(A2:I2), and array-enter it (Ctrl-Shift-Enter), and that should do it.

    Regards

    Bob

    • Post Points: 5
Page 1 of 1 (2 items) | RSS
Copyright Excel User Group and the relevant contributors, 2008. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.