Microsoft Excel help! (keyboard, email, screen, icon)

[Anchorless]

Okay, I have to make a list of contacts, but I'm really terrible with Excel.

Here's what I have to do. I have to make a contact database, and it's fairly voluminous. Luckily I can pull the info from the internet, so it should just be a matter of copy/drag/paste. I originally set it up as a Word document with 2 columns to conserve space, and because it was pretty quick to do. My boss thought there would be a problem doing it that way, especially if we have to do mail merge crap (mass mailings, letters, envelopes, etc.). She said I should set it up on Excel, even though it will take 10x longer.

Here's an example:

John S. Smith
Smith Bank
111 Street Rd.
Ste. 100
New York, NY 55555-5555
(555) 555-5555
jssmith@smithbank.com
www.smithbank.com

That's the info I have to work with. Obviously just dragging that from the website to a Word Doc it easiest. Question: Is there any fast way to do it on Excel, without having to set up individual columns for each line (name, firm, street, city, state, zip, phone, email, website), and then having to copy and paste each individual line into each individual cell? Also, I have to hyperlink the website and email address.

I've had a few bits of advice:

First was to copy and paste everything into notepad as it is, then write a program to parse a .txt file and ouput a delimited file. I could then open delimited file in excel. Problem is I don't know what any of that means.

Someone also suggested that I could write (record) a macro that would cut and paste the appropriate info based on where the cursor is. Something about regular expressions. They also said to make it one repetitive line (and lose the columns.) Again, don't know.

I thought about downloading some online software (Listgrabber), but it's really expensive.

Any ideas? Thanks!

[Mike in TN]

I have a question myself.

I have a workbook that has columns A-G hidden. I can't figure out how to unhide them.

[alleycat]

Originally Posted by Mike in TN

I have a question myself.

I have a workbook that has columns A-G hidden. I can't figure out how to unhide them.

Do a "select all" by clicking the upper left corner (the empty space on the same line as the column letters), then go to Format > Column > Unhide. I think that should do it.

[alleycat]

Anchorless, are you talking about hundreds of contacts, or thousands?

[alleycat]

Originally Posted by Anchorless

First was to copy and paste everything into notepad as it is, then write a program to parse a .txt file and ouput a delimited file. I could then open delimited file in excel. Problem is I don't know what any of that means.

I think I can explain that line by line.

First was to copy and paste everything into notepad as it is...
I assume you don't have any problem with this part, it's just a cut-and-paste operation.

...then write a program to parse a .txt file...
In this case, they mean write a program to turn this:

John M. Smith
123 Main Street
Anytown, MA 12345

Into this:
John M. Smith [tab]123 Main Street [tab]Anytown, Ma 12345
-The [tab]s are just there to show you that each line has been tabbed to the correct position. It's hard to show the correct format here on the forum because of the way the reply box work.

You can do this manually (tab the lines) if you don't have too many to do. That's why I asked whether you had hundreds or thousands.

...and output a delimited file.
Forget this part. Excel can take care of this. A delimiter is just something that Excel can recognize as a "separator", usually a tab, but it can be something else.

Once you have a notepad txt file, then it's an easy matter to import the data directly into an Excel worksheet. You would probably want to run a test first to be sure your delimiters (tabs) work as you want them to, else Excel might add empty columns where you don't want them. There are other ways to work around this, but it's nice if it's all set up correctly.

Does that help?

[Anchorless]

Originally Posted by alleycat

Anchorless, are you talking about hundreds of contacts, or thousands?

About 300 or so.

[alleycat]

Three hundred, huh? Well, it would be a boring job, but you could do it manually.

If I get time, I'll see if I can find a canned program to do this for you. I'm sure something is already out there . . . somewhere.

[Anchorless]

alleycat:

The above does work, however it seems as though it would take just as long as copying and pasting the lines into an Excel spreadsheet (because I have to manually tab all of the fields).

Also each entry that I'm pulling originally comes with a title introduction that I have to manually delete:

Name: John S. Smith
Address: XXX St., New York, NY, XXXXX
Firm:
Phone:

Etc.

I'm still going to look for other ways, I guess.

[Anchorless]

Originally Posted by alleycat

Three hundred, huh? Well, it would be a boring job, but you could do it manually.

If I get time, I'll see if I can find a canned program to do this for you. I'm sure something is already out there . . . somewhere.

There is something called ListGrabber that I might be able to use for a trial period to do just this job.

I'm looking into that.

Thanks for your help!

[Tek_Freek]

Originally Posted by alleycat

I think I can explain that line by line.

First was to copy and paste everything into notepad as it is...
I assume you don't have any problem with this part, it's just a cut-and-paste operation.

...then write a program to parse a .txt file...
In this case, they mean write a program to turn this:

John M. Smith
123 Main Street
Anytown, MA 12345

Into this:
John M. Smith [tab]123 Main Street [tab]Anytown, Ma 12345
-The [tab]s are just there to show you that each line has been tabbed to the correct position. It's hard to show the correct format here on the forum because of the way the reply box work.

You can do this manually (tab the lines) if you don't have too many to do. That's why I asked whether you had hundreds or thousands.

...and output a delimited file.
Forget this part. Excel can take care of this. A delimiter is just something that Excel can recognize as a "separator", usually a tab, but it can be something else.

Once you have a notepad txt file, then it's an easy matter to import the data directly into an Excel worksheet. You would probably want to run a test first to be sure your delimiters (tabs) work as you want them to, else Excel might add empty columns where you don't want them. There are other ways to work around this, but it's nice if it's all set up correctly.

Does that help?

I would use this excellent advice, but do the work in Word for this reason: macros! Assuming (I know, I know) that each address has the same info as this one, no more, no less, then a macro can turn it into a delimited file.

Starting here in a Word document:

Name: John M. Smith
Address: 123 Main Street
City: Anytown, MA 12345

Place the cursor directly before the N in Name.

Click on Tools/Macro/Record new macro

and you will get a small box on the screen "Record Macro". Give it a name (no spaces) and click on the keyboard icon. On the next screen the program wants you to choose a key combination for the macro. Any current combos are listed at the left side. Choose one that you can remember. Alt/a for addresses perhaps, or alt/c for convert. Press the key combination and you will notice the screen changes. Click Assign after you do this.

Your combo is at the left if you did it correctly. Click Close.

Now you will see a small box on the screen at top left. This is the recorder, and it is already running. Notice the icon. It should look like a cassette. If not you aren't recording.

Press and hold [Crtl] and press [Delete] to remove Name. You may have to do it again to remove the [colon]. Press the [End] key to place the cursor at the end of the name line. Press either [comma] or [tab] not both, though. Either one denotes a delimit character to excel. I have a tendency to use a comma because I can see it and know it's been added. Press [Delete] to pull the address up to this line. Press [Ctrl][Delete] combo to remove the address: then [End] to move the cursor to the end of the line again. [Comma] for the delimit, [Delete] to pull up the next line. Repeat [End][Comma][Delete]([Ctrl][Delete]) until all lines are on a single line with commas separating them.

If you have another address block below this then you want to press the down arrow key to move the cursor to the next line down. If this is a blank line then press [Delete] to bring the address block up to this line.

Your cursor should now be in the same relative location as it was when the macro recording started. Except it's at the front of the second block.

Move to the small box that opened at the top left and click on the square to stop the recording. If you hold the cursor over the button it will display Stop Recording.

If you used a comma your address blocks should look like this with the cursor to the left of Name:

John M. Smith,123 Main Street ,Anytown, MA 12345
Name: Bill M. Smith
Address: 123 Main Street
City: Anytown, MA 123455

Notice some blanks after the commas. This is from the lines I copied from your post. They may or may not be in your original, and to be honest I don't think it matters.

Clear this document, open it again, and run the macro. Does it do what it's supposed to?

If so post back and we'll move on to altering the macro so it will run until it reaches the end of the document. This will change the entire list then stop when it finishes the last one.

Hope I got all this correct!!