Need output of "find" function

Art2Gecko

New Member
Joined
Jul 9, 2002
Messages
16
This is probably an easy solution, but not sure how to do it.. I have a worksheet of people's contact info imported from a text file. The data I have in the excel sheet is all in column "A". What I need is to extract just all the email addresses from this long list of data. I tried using the "find" pulldown funtion, under the "edit" toolbar selection, and this works fine, doing a "find" for the "@" symbol, unfortunately, I'm not sure how to output just the results of that "find" search....just the email addresses...

thanks!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Have you tried the FIND formulae function.

I've used this in the past to first FIND the '@', then using that as a reference FIND the postion of all the '.' and start & end <spaces>.

Once I know the position of the start & end <spaces> then you use MID formulae function.

Someone else might know an easier way!!
 
Upvote 0
Have a look at Data;Text to Columns.

Depending on the text file you got it from if its delimited or csv you may find that excel will drop the e-mail addresses into one column. Then just delete the rest.
 
Upvote 0
Is doing a Text to columns operation not an option?
(Then you could have all your email addresses in their own column.)
 
Upvote 0
With that layout text to columns won't help.
You could always loop through the column and put what you're looking for into another column.
Here's an example that'll put just the email addresses (as defined by a string containg the "@" character) in column Z.
Code:
Sub PullOutEmailAddresses()
Dim c As Range, Rng As Range
LstRw = Columns(1).Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set Rng = Range(Cells(1, 1), Cells(LstRw, 1))

For Each c In Rng
    If InStr(c, "@") Then Cells(LstRw, 26).End(xlUp)(2, 1).Value = c.Value
Next c

End Sub
Hope it helps.
 
Upvote 0
going though all the options on importing the data, and also trying the text to columns, doesn't seem to give the email addys their own column..
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top