Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: extracting text from a field

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Liverpool UK
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have 2000 fields in a column, with garbled text in them apart from an email address in each which I want to extract, in each case the e-mail address is surrounded by < > how would I extract the address only to another field

    ie in A1

    fjdhflkjsjdhjaldhskjhbjiduegduqhuhwd

    i wantin A2 myemail@blahblah.co.uk

    any help would be appreciated

    [ This Message was edited by: steven H on 2002-03-27 10:04 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    The formula below should do the trick (although I have a feeling it could probably be simplified)...

    =MID(A1,FIND("<",A1)+1,FIND(">",A1)-FIND("<",A1)-1)

    Rgds
    AJ

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-27 10:03, steven H wrote:
    I have 2000 fields in a column, with garbled text in them apart from an email address in each which I want to extract, in each case the e-mail address is surrounded by < > how would I extract the address only to another field

    ie in A1

    fjdhflkjsjdhjaldhskjhbjiduegduqhuhwd

    i wantin A2 myemail@blahblah.co.uk

    any help would be appreciated

    [ This Message was edited by: steven H on 2002-03-27 10:04 ]
    =MID(A1,SEARCH("<",A1)+1,SEARCH(">",A1)-SEARCH("<",A1)-1)

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Liverpool UK
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin

    Thats worked fine thanks for the prompt reply, but the problem is (AS I JUST FOUND OUT) there may be more than one e-mail address within the field breacketed <> in the same way and I need to return both....

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-27 10:25, steven H wrote:
    Aladin

    Thats worked fine thanks for the prompt reply, but the problem is (AS I JUST FOUND OUT) there may be more than one e-mail address within the field breacketed <> in the same way and I need to return both....
    Note that AJ's formula is identical to the one I posted.

    In B1 enter:

    =MID(A1,SEARCH("<",A1)+1,SEARCH(">",A1)-SEARCH("<",A1)-1)

    [which you are already given ]

    In C1 enter:

    =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"<",""))>1,SUBSTITUTE(A1,"<"&B1&">",""),"")

    In D1 enter:

    =IF(LEN(C1),MID(C1,SEARCH("<",C1)+1,SEARCH(">",C1)-SEARCH("<",C1)-1),"")

    Repeat the logic of the above if you have more than 2 emails buried in A1.

    Aladin


  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Houston, TX
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Seems like a lot of people here don't like VBA solutions, but here's one if you want it.

    This subroutine would put all email addy's in A2, with a space between them.

    Private Sub GetEmail()
    Dim intCnt As Integer, _
    intLen As Integer
    Dim strChar As String, _
    strData As String, _
    strEmail As String
    Dim blnFound As Boolean

    strData = ActiveSheet.Range("A1")
    intLen = Len(strData)
    For intCnt = 1 To intLen
    strChar = Mid(strData, intCnt, 1)
    If strChar = "<" Then
    blnFound = True
    ElseIf strChar = ">" Then
    blnFound = False
    strEmail = strEmail & " "
    ElseIf blnFound Then
    strEmail = strEmail & strChar
    End If
    Next intCnt

    ActiveSheet.Range("A2") = strEmail
    End Sub

    I just through this together, but it should work.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Boston, Mass
    Posts
    182
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could try... Find and replace each sign; greater than (>) and less than (<) with a character like ( | ). (Obviously not a letter or @ sign that resides in your text.)
    Then choose Text to Columns, delimited, other (put in the character you chose | ). You have options such as skipping a column and choosing a destination cell.

    HTH
    Drew

  8. #8
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-27 11:11, LarryJ wrote:
    Seems like a lot of people here don't like VBA solutions, but here's one if you want it.
    I love VBA solutions. I care not one jot for builtin functions. (except for SUM)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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