extracting text from a field
Find bottlenecks in your Excel workbooks
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
    81,790
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 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
    81,790
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 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)

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
  •  

 

 
DMCA.com