Customer and supplier lists for QuickBooks™: names, duplicates and line breaks in Excel™
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
- Names containing a colon or a quote.
- Duplicates, including ones that differ only by case or a hidden space.
- Headings that do not match what the import expects, and the tab name.
- Blank cells in required columns.
- Line breaks inside cells, which split one record into two when the file is saved as CSV.
- A semicolon-separated file that landed in one column on opening.
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.
- Search names with Ctrl+F for
:and for". - Find duplicates with a cleaned helper column (
=UPPER(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))) and=COUNTIF(B:B,B2). Filter for counts above 1. - 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.
- Check blanks with Home → Find & Select → Go To Special → Blanks over the required columns.
- If everything landed in column A, select it and use Data → Text to Columns, choose Delimited and tick Semicolon.
- 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.