AppGarden Import Cleaner for QuickBooks logo AppGarden

Import Cleaner for QuickBooks™

Customer and supplier lists for QuickBooks™: names, duplicates and line breaks in Excel™

By Paul Kemp · Published

A customers and suppliers file looks simple, but small things can trip an import: a name with a colon or a quote in it, the same customer listed twice, or an address with a line break that a CSV turns into a new row.

What to check

Check Intuit's current guidance for which characters and columns apply to your plan.

Check by hand

These steps apply to Excel™ in Microsoft 365. Work on a copy.

  1. Search names with Ctrl+F for : and for ".
  2. Find duplicates with a cleaned helper column (=UPPER(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))) and =COUNTIF(B:B,B2). Filter for counts above 1.
  3. Find line breaks with Ctrl+F: click in the Find box, press Ctrl+J to enter a line break, and search. Replace each with a space.
  4. Check blanks with Home → Find & Select → Go To Special → Blanks over the required columns.
  5. If everything landed in column A, select it and use Data → Text to Columns, choose Delimited and tick Semicolon.
  6. Compare the headings letter by letter with the template.

Let the pane read the list

Import Cleaner for QuickBooks™ checks customers and suppliers for names with a colon or quote, duplicates, headings, the tab name and blanks (QI-08), line breaks inside cells and a semicolon-separated file landing in one column (QI-12), and spaces in blank cells and around values (QI-15). The scan and explanations are free; applying the fixes is the paid part. It works on the active sheet only and the workbook stays on your machine.

See Import Cleaner for QuickBooks™ for Excel™

Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. QuickBooks is a trademark of Intuit Inc. Import Cleaner for QuickBooks is an independent product, not affiliated with or endorsed by Microsoft or Intuit Inc.