Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Manipulate Text (Excel 2003)

    Is it possible to use Find with Left,Mid, Right to extract a phone number from anywhere within a text field?

    Example:
    MA10001 MATTHEW OF World @ 555-555-1212 PLACED call for mike. REPORTED & APPROVED BY MIKE @ 11:29

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Manipulate Text (Excel 2003)

    Will it be in the same format?
    Will it always be after an @?

    I ask so that there could be something we can "grab"
    Jerry

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulate Text (Excel 2003)

    It will always have the two dashes, but can be anywhere in the text field.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Manipulate Text (Excel 2003)

    Hi There

    Post this into a Module

    Function FindPhone(strPhone As String)

    Dim sText As String
    Dim Start As Integer
    Dim TelPhone As String

    sText = strPhone
    Start = InStr(1, sText, "-")



    TelPhone = Mid(sText, Start - 3, 12)

    FindPhone = TelPhone


    End Function


    And now use as a UDF ie =FindPhone(A1)


    enjoy
    Jerry

  5. #5
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulate Text (Excel 2003)

    Wow- thank you! That is fantastic.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Manipulate Text (Excel 2003)

    Even simpler you could just go for this function <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    =MID(A1,FIND("-",A1,1)-3,12)
    Jerry

  7. #7
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Manipulate Text (Excel 2003)

    The custom function and worksheet formula provided by Jerry only work when there is no other dash preceding the phone number. If this case is a possibility, you can use the SEARCH() worksheet function (which allows wildcards) to look for the pattern "###-###-####" in something like this:

    =MID(A1,SEARCH("???-???-????",A1,1),12)

    If you need to return something non-error in the case of no phone number, wrap a conditional test around the formula like this:

    =IF(ISERR(MID(A1,SEARCH("???-???-????",A1,1),12)),"big ol goose egg",MID(A1,SEARCH("???-???-????",A1,1),12))

    Aloha,
    John Jacobson

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •