There's a reason it rhymes with "hell"

July 25, 2012 1:23 PM

Another "please help me find an alternative or a way to do this in Excel" question.

Mefites turned me on the excellent Notable and Balsamiq, can you help again?

I'm doing a large project that must be in Excel, at least the final version I hand off must be in Excel. I'm cutting and pasting a lot of text from different websites into fields for Name, Address, Hours, Description (about a paragraph), et cetera.

It's all just a nightmare of different fonts and sizes and formats and large blocks of text that make everything wonky and of course I can't actually SEE anything to proofread it and and and oh the misery.

Can anyone suggest a way to curate this data in some other way that will follow the proper organization and then be easily put into the Excel spreadsheet? I'm probably explaining this poorly but any help is greatly appreciated.

posted by 2soxy4mypuppet to computers & internet (15 answers total)

Need more information/screenshots. What's happening that you can't correct by stripping formatting? Why can't you 'see' anything?
posted by MangyCarface at 1:30 PM on July 25, 2012

Can you at least "paste special" without formatting or "paste as plain text"? This should be available via the right-click popup menu, assuming you're on some version of Windows.
posted by Joey Buttafoucault at 1:32 PM on July 25, 2012 [1 favorite]

If I'm understanding your problems correctly -- and, no disrespect intended, you're not being too precise as to what they are -- I would suggest you "clear formatting" and make sure that cells wrap text.
posted by WCityMike at 1:33 PM on July 25, 2012 [2 favorites]

In Excel 2010: Home > Paste > Paste Special... > Text > OK.
posted by davcoo at 1:33 PM on July 25, 2012

Yeah, either paste special > text, or select Match Destination Formatting when you right-click in a cell (a la http://img42.imageshack.us/img42/372/725201243219pm.png)
posted by reptile at 1:34 PM on July 25, 2012 [1 favorite]

Alternatively you could collect it in Notepad before pasting it into Excel.
posted by chesty_a_arthur at 1:35 PM on July 25, 2012 [1 favorite]

I find that if you are copying and pasting from websites into Excel that a mid-point in Word might be helpful.

If you get everything all squished up in one cell, use Text to Column and delimit with tabs or spaces can help.
posted by Ruthless Bunny at 1:35 PM on July 25, 2012

Paste the text into notepad first. Tidy up line breaks etc. Then paste into excel (the formatting is removed automatically). Learn your keyboard shortcuts in notepad...and you will speed through it.
posted by therubettes at 1:37 PM on July 25, 2012

Paste it into a big text file with commas to separate the fields, make sure you wrap the fields in " pairs to escape any embedded commas, then open in Excel as CSV. Your text editor will strip all the formatting, etc.

So you'll have:


"Foo Corp", "123 Fake Street Suite", "9am - 9pm", "This place is ok"
"Bar Corp", "227 Fake Street", "7am - 5pm", "This place is fine"


You probably want to use a clipboard manager to make your life easier as well.\

Also, to paste as just values in Excel (without formatting), the short cut is Alt+E, S, V, Enter.
posted by jeb at 1:37 PM on July 25, 2012

Dumb question -- why can't you "select all" and then format the text consistently?
posted by slidell at 1:38 PM on July 25, 2012

Shortcut in Word or Excel for Special Paste?
posted by zamboni at 1:39 PM on July 25, 2012

Wrapping text! That's what I wanted. Also "special paste." Why there is not a global option for Matching Destination Formatting the way there is in Word is beyond me.

I still HATE Excel for content, but this has made it easier. Thanks for all the quick responses.
posted by 2soxy4mypuppet at 2:04 PM on July 25, 2012

You might be able to further reduce your headaches by importing the data, rather than copying and pasting. It depends on the formatting of the data on the web site. If it's contained within an HTML table, you can import it directly to Excel like this:

Data tab > click "From Web" (on the far left) > paste the URL in the Address box > click Go.

Once the page loads in this little window, you should see a yellow arrow next to any table that exists on the page. Click the yellow arrow closest to your data, and then click the Import button.
posted by SuperSquirrel at 3:13 PM on July 25, 2012

"a nightmare of different fonts and sizes and formats and large blocks of text that make everything wonky"

You're describing something that I have to do too. I just c/p the text from each source in all the weird assorted fonts, colors etc. into the worksheet and then fully format one cell for each field into the complete format that I want for that field. Then I use the fromat painter to convert each of the records for that field into the master fromat. It goes really quickly. If you're lucky and have only one format for the final sheet it's a snap. Proof reading is the final step.
posted by X4ster at 4:17 PM on July 25, 2012 [1 favorite]

Depending on how quickly you'll pick up writing queries and how many pages you're dealing with with the same formatting, you might want to look into a scraper. I use OutWit as a Firefox add-on.

There's an "Export as Excel" ability, and you can grab almost any bit of data off a page as long as there's some consistent start & end tag to latch onto.
posted by RobotHero at 6:11 PM on July 25, 2012 [1 favorite]