Tiny Lever home

How to clean up pasted table data in Excel

You pasted a table from a website and it looks right, but sums come out as zero, sorting is odd, and there are bracketed numbers in the cells. This guide works through the usual problems in the order they tend to show up. The steps use Excel; Google Sheets has equivalents for most of them.

Step 0: work on a copy

Keep the raw paste on one sheet and do your cleaning on another, or save a copy of the file. Several of the fixes below replace data, and it helps to be able to compare.

1. Trim hidden spaces

Web tables often carry leading, trailing or non-breaking spaces that you cannot see. In a spare column use:

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

CHAR(160) is the non-breaking space that TRIM alone does not remove. Fill down, copy the results, and Paste Special, Values over the original.

2. Remove footnote markers

Press Ctrl+H. In Find what type [*], leave Replace with empty, and choose Replace All. The asterisk is a wildcard so this clears [1], [12] and [a]. Review the result: it also removes any legitimate text inside square brackets.

3. Turn text into real numbers

Signs that a number is text: it is left-aligned, a small green triangle appears, or SUM ignores it. Common causes and fixes:

  • Currency symbols and thousands commas. Use Find and Replace to remove $ and commas, or a formula such as =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")). Locale matters: where the comma is the decimal mark, removing commas will give the wrong value, so check one or two by hand.
  • Percentages. Text like "12%" can usually be converted with VALUE; Excel reads it as 0.12 and you can format the cell as a percent.
  • Negative values in brackets or with trailing minus. "(1,200)" is accounting style for -1200. Replace the brackets by hand or with a formula before converting.
  • Quick method. Type 1 in an empty cell, copy it, select the text numbers, then Paste Special, Multiply. Cells that Excel can read as numbers become numbers. Cells it cannot read stay unchanged, so scan for leftovers.

4. Fill in the gaps left by merged cells

Select the column, press Ctrl+G, then Special, Blanks, OK. Type = and press the Up arrow, then Ctrl+Enter. Every blank now points to the value above. Copy the column and Paste Special, Values to make the fill permanent. If the sheet still contains merged cells, select them and use Merge & Center, Unmerge first, since sorting and filtering refuse to work well over merged ranges.

5. Fix dates carefully

If dates are left-aligned they are probably text. For a consistent format such as 2025-04-03, the DATEVALUE function or Data, Text to Columns (choose Date and a field order) will convert them. The risk is ambiguity: 03/04/2025 can be 3 April or 4 March. Do not bulk-convert until you know which the source uses; look for a date in the table with a day above 12 to tell.

6. Remove empty and duplicate rows

  • Duplicates: select the data, then Data, Remove Duplicates, and choose which columns decide what counts as a duplicate. Be aware that two truly separate records with identical values will be merged.
  • Empty rows: select a column, Home, Find & Select, Go To Special, Blanks, then Home, Delete, Delete Sheet Rows. Only do this when a single column is always filled for real rows; otherwise you will delete rows that have gaps.

7. Check your work

  • Compare the row count and totals with the web page.
  • Sort a numeric column. If the order looks alphabetical (10 before 9), it is still text.
  • Use Filter on each column and scan the unique values for typos and stray characters.

When this keeps happening

If you clean tables from websites every week, the same seven steps get tedious. TableTidy, our Chrome extension, does the common ones in one click before the data reaches your spreadsheet: merged cells, footnote markers, currency and thousands separators into real numbers, unambiguous dates into real dates, and empty and duplicate rows. Copying (up to 250 rows) is free, and Pro, a one-time purchase, adds CSV and Excel downloads and exporting every table on a page to one workbook. Data stays in your browser. It has not launched yet; ask us for a launch email. The manual steps above work either way.