900 names with e-mail addy's under them

[Scarwalker]

I need to take the e-mail adresses out from under a column and copy them into a seperate collumn, or how to get the e mail adresses and names into Outlook ? That is my ultimate goal. Seperate the single column with the e-mail addy's as links under the names into either 2 seperate columns in Excel or into the Name and Email adress feilds in Outlook. Seperating them would do.
This message was edited by Scarwalker on 2002-11-06 12:58
This message was edited by Scarwalker on 2002-11-06 13:09
This message was edited by Scarwalker on 2002-11-06 13:11

 

[stevebausch]

I am a bit confused by the "under a column" statement.

Do you mean to say the names are in, say, even rows, and the addresses are in odd rows?

Or, do you mean that the text has "wrapped", or perhaps Alt-Enter was used to force a second line in the cell?

See what I am getting at here? What's the pattern we can take advantage of? If all the address are in odd/even rows, that's pretty easy. Since all the addresses have an "@" contained, that could come in handy also.


If the text is all in one cell, and an Alt-Enter has been used to create a newline, and that newline is the email address, that's something to work with also.


So, what's the pattern, Kenneth?

 

[Scarwalker]

As I said :columns are usually up and down right? Is the right word Row? Rows are usually horizontal, but Microsoft would call a column a row wouldn't they. The things labeled A B C D and when you highlight them they go from the top of the page to the bottom? And look like columns? Those. Each entry which is a persons name,has a link with an E mail adress. Ever seen an entry in an Excel sheet that contains a link? Thought that was clear to. Well picture this then.....You see a name in a cell. The name is blue not black. You click it and the email adress link under it spawns an e mail message? A name with an e mail adress or web page. That was clear I thought as well. Sorry

I could open the hyperlink page 900 times for each name and the adress under it/ linked to it and take each adress off individually and copy it into a new row/ column whatever, and take forever or.......I could seperate them by some method into the two types of info....names.....and the linked e-mail adresses. I thought openning 900 seperate names to copy the linked e amail adresses was time comsuming.

Fot those who can't understand the first post, don't answer if you can't figure out how a name can be linked to an e-mail address. Please. I have spent 6 hours trying to figure this out and if I have to seperate them 1 at a time so be, but I would rather not as I have 900 web pages linked to names to do as well. That is 1800 multistep processes and that is alot of work. Outlook has a field for Names and E-mail Adresses and, as I said, I want the names and adresses seperated so I can put them in the Outlook feilds.
This message was edited by Scarwalker on 2002-11-06 14:15

 

[kskinne]

hello scarwalker and welcome to the board

i am going to go on the assumption that, for sake of an example, your data is organized as follows: in cell A1, you have a name, then in cell A2, you have a corresponding email address, then in cell A3 you have another name, in cell A4, the corresponding email address, and so on down the column. if this is the case, then you could proceed as follows:

assuming your data is laid out in column A as I specified above, place this formula in cell B1:

=IF(ISERROR(FIND("@",A1)),"",A1)

then copy this formula all the way down column B until you reach the end of you data series. at this point what the formula should be returning is the email address for the rows that contain an email address, and a blank for the rows that do not.

now, highlight your entire range of formulas in column that you just created, then go to the edit menu and select 'copy'. then go to the edit menu again and select 'paste special', and select 'values', and press OK. this converts those formulas to values.

now, while your range of data in column B is still highlighted, press the F5 key, press the 'special' button, and select 'blanks'. press OK. all of the blank cells in your data range should now be highlighted. then press Ctrl+- (the minus key) and select 'shift cells up'. now all that should remain in column B is the email addresses with no blank cells between them.

now highlight your entire range of data in column A. Press Ctrl+H, and in the 'find what' field, type "*@*" without the quotes, leave the replace with field blank. Press the replace all button. now all that should remain in column A are the names.

modify this process as needed to fulfill your requirements. did this take care of your problem? if it didn't then you need to do a better job of explaining your problem, because stevebausch wasn't the only one confused by your initial post.

hth
kskinne
This message was edited by kskinne on 2002-11-06 14:31

 

[Scarwalker]

Hm. Hyperlink page not specific enough? You have never seen a highlighted hyperlink? Default Blue? Try Reading you guys! What is so **** difficult. Here you can recreate 1 cell and finally understand HYPERLINK. HYPRLINKING IS EASTY FROM THE HYPERLINK OPTION YOU GET WHEN RIGHT CLICKING> WHAT IS SO **** CONFUSING ABOUT HYPERLINKS TO YOU GUYS????????? yOU JUST IGNORE THE WORD WHEN YOU READ IT?????????? EXCELL MAN MY ***!


cOULD SOMEONE WHO KNOWS WHAT A HYPERLINK IS PLEASE ANSWER MY QUESTION OR JUST NOT BOTHER

BOTH POSTS STATE HYPERLINKED NAME AND ADRESS IN ONE CELL.

IF YOU DON'T WANT TO READ THE WORD HYPERLINK AND CAN'T FIGURE OUT HOW A CELL CAN HAVE A NAME ENTERED AND A HYPERLINKED E MAIL ADRESS THEN DON'T ANSWER.

Man how plain can i get. Here make a cell like the magic cell I am talking about.


Step 1: TYPE ANY &&^#$#^%Name into the cell. Tom , **** or Haryy will work...a name....means........ A NAME

Step 2:Right click and select..........here is the big moment..........HYPERLINK!!!!!!!!!!!!!!!! whats so *&^&% mysterious about HYPERLINK????????????

Step3: Select e mail adress and put one in to the space provided! Youy have now hyperlinked an email adress to a name! What is so (&*^^mysterious or unexplained about the word hyperlink? an e-mail adress to a name?

Now that the NAME has the E-MAIL ADRESS HYPERLINKED to it ....IT TURNS BLUE!!!!!!!!!!

as i said clearly before!!!!!!!!!!!
Now have made one. Go back to the first post and see how plain simple and in one sentence the post is.

GOT it?

Understand hyperlink
Understand name hyperliked to e- mail adress?
understand blue?
GOOD!
Sheesh you guys should try reading or not bothering to answer. "Seperate the single column with the e-mail addy's as links under the names into either 2 seperate columns in Excel or into the Name and Email adress feilds in Outlook." second sentance in my first post! I thought you were the experts and I was the novice! I the changed the common and usually understood "links" to "hyperlinks" in the second post.


Now that you have a pretty blue name that has under it a hyperlink to an e- mail adress..........NOW back to my question for the third time. can you seperate them 900 at a time or 1 at a time????????? Re read the first post over and over untill you get it or don't reply

NO A1 isn't a NAME and A2 an E mail adress they are HYPERLINKED!!!!!!!!!!!!!!!! or as 99% of the population says....linked.

Sheesh...

 

[Scarwalker]

Skip it.you guys suck.copied my first post and got an imidiate answer from some one who can read the words " name linked to an e-mail adress." and not ignore them.

 

[zacemmel]

In the future if you are asking for help, it may be wise not to ask in a "hostile" manner.

By the way, your failure to communicate effecively is NOT our problem.

 

[Scarwalker]

I quote,"i am going to go on the assumption that, for sake of an example, your data is organized as follows: in cell A1, you have a name, then in cell A2, you have a corresponding email address, then in cell A3 you have another name, in cell A4, the corresponding email address, and so on down the column. if this is the case, then you could proceed as follows: "

Just answer one question...why would someone who understands the word "hyperlink" write this in responce to this -"Each entry, which is a persons name,has a link with an E mail adress. Ever seen an entry in an Excel sheet that contains a link? Thought that was clear to. Well picture this then.....You see a name in a cell. The name is blue not black. You click it and the email adress link under it spawns an e mail message? A name with an e mail adress or web page. That was clear I thought as well. Sorry "?

It is crystal clear by my second post......and "I assume blah blah..."only shows that you can't read twice.

I came for help, asked for specific help, expanded the specificity and still got an answer completely unrelated to hyperlinks.

Read and if you can't figure out how an e-mail adress and a name can be in 1 cell because they are hyperlinked then don't answer.
And I will not apologise for loosing my temper until you apologise for nor reading the words link and hyperlink. I know you won't answer Why your responce should be considered as if you read the question, but maybe you will answer these 2 simple questions.

Have you ever seen a hyperlink (a word in blue, that, when you click it, opens an e-mail message, or a web page) inside an Excel spread sheet?

If you have seen a hyperlink in a page have you ever made one?

If you have ever made a cell with a link, what is so confusing about my posts?

If you haven't....why did you answer? To deliberately waste my time?

If you have made a link before....why did you ignore that part of BOTH posts?

Looking forward to seeing which questions you don't answer.

 

[Alriemer]

Scarwalker,

Tried your suggestion - opened new workbook, typed "Harry" in arbitrary cell, right-clicked, but Hyperlink option was greyed out.

When you select the cell, what do you see in formula bar: just "Harry" etc or something like =HYPERLINK("harry@email.com","Harry")

If the former, solution probably requires VBA, beyond my ability.
This message was edited by Alriemer on 2002-11-06 16:51

 

[Scarwalker]

You may not believe this but...thanks. Really, by saying you can't help it helps. I can now concentrate on other area's. It Seems, so far, for your informatiom, that they cannot be seperated in Excel just like that(snap)! I saved the column in html and, in a text editor(notepad) opened the html. It shows as the code including the adresses! I have to manually delete the code leaving the adresses, but luckily there are seperate lines for each entry.As I am deleting the code the adresses are lining up ina colum in order! Once the code is all deleted I will be left with a column which I copy and paste back into Excel!. Hows that for a runaround, but it is still much faster than using that hyperlink window. I wonder why it was greyed out for you? Hmmm.
Thanks again!