Summary
Learn how to ensure your account mapping is accurate, and why it matters in MindBridge.
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 2 digits:
=TEXT(A2,"000000.00")
Note: You can replace periods (.) with other account separator characters, such as hyphens (-).
Anything else on your mind? Chat with us or submit a request for further assistance.