extracting text from a field

[steven H]

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 ]

[AJ]

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

[Aladin Akyurek]

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)

[steven H]

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....

[Aladin Akyurek]

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

[LarryJ]

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.

[Drew]

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

[Mark O'Brien]

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)