Fix MYOB Account Numbers Exported to Excel

It used to be simple to export data from MYOB AccountRight to Excel.  With more recent versions, the export has changed which means all your formulas looking for particular account numbers won’t find them.  Here’s how you can fix MYOB account numbers.

The Cause

When MYOB exports its account numbers, it actually adds a non-printable character to the end of every account number.

This isn’t visible in Excel, so it looks like everything should match … but it doesn’t.

That’s very frustrating because all your MATCH/VLOOKUP/HLOOKUP/INDEX formulas stop working!  Grrrr!

You can fix this by using the CLEAN function in Excel.

This removes all non-printable characters from a cell and leaves you with just visible characters.

How to Fix MYOB Account Numbers

Download the file below for a quick worked example.