Questions? We have answers.

Account Number Inconsistencies - Why and How to Fix for Accurate Account Mapping

Article author
Kate H
  • Updated

Symptoms

Some account numbers have leading zeroes and/or a specific number of digits after a decimal place, but after importing into MindBridge or even opening in Excel, the leading and/or trailing zeroes disappear. For example, 001005.00 becomes 1005

This can cause problems with account mapping if the account number format is not the same across the general ledger, trial balance, and chart of accounts. During account mapping, our system will think that 001005.00 and 1005 are different account numbers.


Resolution

  • If you open a CSV or text file in Excel, be sure to select Text format in the Text Import Wizard for account numbers. Why? Because with the default General format, Excel treats what looks like a number as a number and strips leading 0s and trailing 0s in decimal digits. See Step 3 in Converting Text Files (delimited or fixed width) to Excel
  • What if the file you were given already has leading and/or trailing zeroes removed and you want to put them back? The TEXT() Excel function handles this kind of number formatting. The following formula forces all account numbers to be 6 digits followed by . followed by two digits:
=TEXT(A2,"000000.00")

mceclip0.png

Note that you can replace the . with other account separator characters such as -

 


Anything else on your mind?
Log in to MindBridge to chat with us, submit a request, or reach out to your assigned Customer Success Manager.


Related Articles

 

Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.