Excel formula help

[James]

Hi,

I have just obtained a spreadsheet with hundreds of addresses, names & email
addresses in column A1 downwards. What I want to do is extract the email
adderesses from column A to column B. Once done I can then filter the Email
addreses & send my CV out to them by putting them all in my address book (I
am a contractor & will need to send it out once every 2 or 3 months.

Any help would be appreciated - thanks in advance

James

 

[Beth Melton]

Are you saying the name and email address is in the same cell?

If that's the case then you might be able to use Data/Text to Columns
to separate the data. How well this will work depends on how the data
is delimited so you may need to use Find/Replace to clean-up your data
first.

For example if there is a new line after the Name, Address, email
address, etc then you can use Edit/Replace to replace the new line
(press Alt + 010 in the Find text box) and replace with a character
not used in your data, such as a $. Then you can use the Text to
Columns command an use the $ as your delimiter.

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/

 

[Bob I]

Open file, DELETE everything after column B, Save As ".csv",(VERY
IMPORTANT to not just SAVE)

 

[Beth Melton]

A CSV file format will create a comma separated text file and the
comma delimiter is added between the content of each individual cell.
IOW, if the name, address, email address is contained in a single cell
then this really won't do anything.

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/

 

[James]

Beth,

Thanks for your help but the way it is laid out is as follows;

Street
Place
City
Postcode
Country
Phone:
Fax:
(e-mail address removed)

Street being A1
Place A2
City A3
etc
etc

Each line of text is in a differnet cell. Is there a formuls to say if
there is a @ symbol copy it to say cell B1 & so forth?

Thanks

 

[Bob I]

AH, negatory on my last, with that layout it won't work.

Make B1 contain
=IF((FIND("@",A1,1))>0,A1,"")
B2
=IF((FIND("@",A2,1))>0,A2,"")
Copy on down

And then Copy, Paste Special, Value, Column B to convert from formula to
Values.

Then sort to Strip out the "#Value!"

 

[Beth Melton]

Gotcha. Providing a sample of your data is always helpful.

Based on what you provided, if all of the email address are in same
respective place, 8th line in the record, then it should just be a
matter of transposing your data - especially if you want to filter and
perhaps use the addresses in a Word mail merge for your CV. Actually,
even if each entry doesn't have the same number of lines you'll still
want to transpose your data. It will just take a little more clean up
to get the proper layout.

The layout should be:

A1: Street
B1: Place
C1: City

Etc

Now, if there is an empty row between each address then you'll need to
delete those first. Select column A and go to Edit/Go To and click the
Special command. There you'll find a "Blanks" option. Select it and
click OK. Spot check your data and make sure only the empty rows are
selected. Once verified go to Edit/Delete and use the "Rows" option.

Then to transpose your data use the following steps:
- Select the data to be transposed
- Go to Edit/Copy
- Select another worksheet
- Go to Edit/Paste Special and turn on "Transpose" and click OK

Once this is accomplished you should be able to single out the email
address field for filtering, merging, etc.

Also, make sure your first row contains field names so you can easily
sort, filter, etc.

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/