How to Clean Data Copied From a Website in Excel

You copy a table off a webpage or out of a PDF, paste it into Excel, and it looks fine for about three seconds. Then you notice cells with line breaks buried inside them, little boxes and bullets where real characters should be, a stray "Page 1 of 3" halfway down, and a total column that refuses to add up. To clean data copied from a website in Excel, you're not fixing the data so much as scrubbing out the junk the browser or PDF stapled to it on the way in.

This is the paste-cleanup step aimed at anyone pulling data off the web, using free macros for the fixes that formulas make fiddly.

TL;DR: clean data copied from a website in Excel

• Kill the line breaks inside cells. Web and PDF copy drops hidden newlines that break sorting and lookups.
• Strip the special characters. Bullets, non-breaking spaces, and symbols come along for the ride.
• Cut the repeated junk. Page numbers and repeated headers get pasted right into your data.
• Fix numbers stored as text. Pasted numbers usually land as text and won't sum.
• Trim spaces and dedupe. The finishing pass that makes lookups and pivots behave.

With the macros loaded this is a two-minute pass. The alternative is an afternoon of squinting at cells that look clean but aren't.

What copy-paste actually dumps into your cells

A webpage and a PDF store their layout as structure, not as a grid. When you copy from them, Excel has to guess how that structure maps to rows and columns, and it guesses badly. So a single cell arrives holding two lines of text joined by a hidden line break. A price shows up as 1,299 kr with a non-breaking space that stops it summing. A bullet character or a footnote marker rides along inside a name.

None of this shows up as an error. The cell looks right, and then a sort scatters your rows, a VLOOKUP misses a match it should make, and a total comes out wrong. Microsoft's top ten ways to clean your data covers the ideas, but on a real paste you want the fixes done in one pass, not cell by cell.

Step 1: Strip the line breaks inside cells

This is the big one. Copy from the web or a PDF and you get hidden newlines and tabs sitting inside individual cells, which is why one row is suddenly three lines tall and your sort makes no sense.

Select the pasted range and run Remove Line Breaks from Cells. It replaces every carriage return, line feed, and tab with a single space, then collapses runs of spaces, so each cell holds one clean line. It skips formula cells, so nothing you built gets touched. New to running macros? The 30-second setup gets you going.

Step 2: Remove the special characters

Next, the symbols. Web and PDF text is full of characters that look like blanks or boxes: non-breaking spaces, bullets, curly quotes, footnote markers, and non-printable control codes.

Run Remove Special Characters. It asks what to keep, letters and digits only, or those plus spaces, or plus basic punctuation, and strips everything else from each cell. For most web data, keeping letters, digits, and spaces clears the junk while leaving your words intact. If you need periods and hyphens (in prices or product codes), pick the option that keeps basic punctuation.

Step 3: Cut the repeated junk

Scraped and PDF-pasted data loves to include things that aren't data: Page 1 of 3, a column header repeated on every page, a "continued" marker. The fastest fix is Find and Replace. Press Ctrl + H, type the repeated text, leave Replace empty, and Replace All.

If there are several such artifacts, list each one and blank it in a single pass with the Bulk Find and Replace From Table macro, which applies a whole table of find-and-replace pairs at once instead of running Ctrl+H a dozen times.

Step 4: Fix numbers stored as text

Pasted numbers almost always arrive as text, left-aligned and un-summable, often carrying that non-breaking space from Step 2. Select the value columns and run Convert Text to Numbers. It strips the leftover formatting and writes a real number, so SUM and your pivots finally work. Leave ID and code columns out of the selection so their leading zeros survive.

Step 5: Trim spaces and remove duplicates

Finish with the tidy-up. Run Trim Whitespace From All Cells to clear leading, trailing, and doubled spaces that the paste left behind. Then, if the page gave you repeated rows, select the data with its header and run Remove Duplicates by Multiple Columns to drop the copies. Now the data sorts, filters, and looks up the way it should.

The hot take: it's a cell-contents problem, not a columns problem

Every guide to messy pasted data jumps straight to Text to Columns. Sometimes you do need it, but for web and PDF copy it usually misses the real issue, which is what's stuffed inside each cell, not how the cells are split. Line breaks and hidden characters don't care how you slice the columns.

Clean the contents first, the newlines, the symbols, the text-numbers, and half the problems you were about to solve with Text to Columns simply vanish. Rearranging columns around dirty cell contents is polishing a window you haven't washed.

Common pitfalls

• Splitting before you clean. Run Text to Columns on cells full of hidden line breaks and you get a mess in more columns. Clean the contents first.
• Over-stripping characters. Keeping letters and digits only will delete the dots in prices and the hyphens in codes. Pick the mode that keeps the punctuation you need.
• Converting code columns to numbers. Order numbers and zip codes with leading zeros lose them when forced numeric. Only convert real value columns.
• Cleaning the live paste. Paste as values first (Paste Special, Values), so you're cleaning data, not chasing web formatting that re-applies itself.

Frequently asked questions

How do I clean data copied from a website in Excel?

Work through the paste artifacts in order: strip the hidden line breaks inside cells, remove special characters, delete repeated junk like page numbers with Find and Replace, convert text-numbers into real numbers, then trim spaces and remove duplicates. Each is a one-click macro, so the whole pass takes a couple of minutes instead of an afternoon.

Why does my pasted data have line breaks inside single cells?

Web pages and PDFs store text with their own line breaks, and copying carries those hidden newlines into the cell. That's why one row appears several lines tall and sorting scrambles the order. The Remove Line Breaks macro replaces those newlines and tabs with single spaces so each cell holds one clean line.

How do I get rid of weird symbols and boxes after pasting?

Those are special or non-printable characters: non-breaking spaces, bullets, curly quotes, and control codes that display as boxes. Run the Remove Special Characters macro and choose to keep letters, digits, and spaces. It strips the rest from every cell at once, which is faster and more thorough than hunting them down with Find and Replace.

Why won't my pasted numbers add up?

They're stored as text, not numbers. Pasted values arrive left-aligned and often carry a non-breaking space that keeps Excel from reading them as numeric, so SUM returns zero. Convert the value columns to real numbers and the totals work. A green triangle in the cell corner is the usual sign a number is really text.

Do these macros work on Excel for Mac?

Yes. The line-break, special-character, convert, trim, and dedupe macros all run on Excel 2016 and later on Windows and Mac. They avoid Windows-only features, so an analyst on either platform gets the same clean result.

What to do next

Paste as values, run the fixes in order, and the data stops fighting you. Clean the cell contents first and the rest falls into place.

To clean data copied from a website in Excel on your next paste, browse the full set of Excel tools for data analysts, or start with the Remove Line Breaks macro, the fix that untangles the most damage. Prepping it for a report next? See how to clean data for a pivot table.