cancel
Showing results for 
Search instead for 
Did you mean: 
marinez
Level 1

Account numbers in a different column - trial balance

We are exporting the trail balance to excel to be imported into a tax program.  We need the account number in one column and the account description in another one.  At this time the account number and the description are on the same column.

 

Thanks

Solved
Best answer January 09, 2018

Best Answers
vpcontroller
Level 11

Account numbers in a different column - trial balance

This is not normal accounting report in QBO.

Unfortunately, it seems there is no way to change account number and account name on two separate columns.

If you don't account number for the tax program, you can temporarily turn off account numbers from the company settings (Gear (⚙) icon at the top right > advanced > chart of accounts > turn off "enable account numbers" box).

 

Hope this helps!

View solution in original post

5 Comments 5
vpcontroller
Level 11

Account numbers in a different column - trial balance

This is not normal accounting report in QBO.

Unfortunately, it seems there is no way to change account number and account name on two separate columns.

If you don't account number for the tax program, you can temporarily turn off account numbers from the company settings (Gear (⚙) icon at the top right > advanced > chart of accounts > turn off "enable account numbers" box).

 

Hope this helps!

Teri
Level 9

Account numbers in a different column - trial balance

How do we request this be changed?  I know it has been this way forever, but is really not user-friendly.

SARAASB
Level 3

Account numbers in a different column - trial balance

It has been this way forever.

I am an accountant who wrote a utility called TBX Trial Balance Exporter back in 2004, which gives you these options. I don't see any sign that the T/B will be changed in Desktop.

 

The names can be split once in Excel, but if you want to save time, try my 3rd party app.

Here is a link to the QuickBooks Desktop App page:

https://desktop.apps.com/apps/167772/tbx-trial-balance-exporter#!overview

sdunahay
Level 1

Account numbers in a different column - trial balance

In Excel there is a "Text to Columns" feature that will separate the account number from the account description.

ESL01
Level 1

Account numbers in a different column - trial balance

Depending on your user access you can't remove or add acct numbers from reports. So you have to work around that... But you don't need to lose your hands clicking on text to columns over and over and over. 

 

Instead, you can also add a blank column and then use right() left() formulas to extract it.

If account numbers are 5 digits, and you have a space-dot-space after that, then you can extract the numbers in a cell before it using =left(B2,7) then you can also remove the dot and spaces by wrapping that up into a substitution formulas as in:  =substitute(left(B2,7)," ● ","") 

 

Doing it this way means you can do it ONCE and just copy/paste formula in the future so you don't have to manually go through that slow multi-step clicking options for each column  - as you know sub accounts all go into further column that you would have to ALSO text-to-column for manually. It can be fully automated by adding a bunch of blank columns (1 for each level-deep your sub accounts go) and using this formula in each column at once. You'll end up with a sort of grid of account numbers, and you can hide all those columns by adding one more that shows only the column to the left (or right) of it that HAS a value not equal to "" (null) .

 

=if(a1="",if(b1="",if(c1="",c1),b1),a1) in this case you go three levels deep max but you can keep going. When your done, hide all the working columns and just leave the one with the =if() statement to show the result -> the one cell that has the account# from all the columns you prepped.

 

ALTERNATIVELY, in the Left() command you can implement this method by only show the left values of a cell IF IT'S NOT blank, and keep looking deeper into the columns until it finds one (you'll know how deep it needs to check). Either way is the same work just a reversed approach. 

 

SO you get a column with the # all by itself and another column (the one you started with) that has the name as well. If you want the number removed from that one, you just remove the X-number of characters that has the #_●_ in it and return the result that excludes it.  And you can save having to think about all this each time you build a report Just copy the working columns into your new report all the way down to the bottom of it after making a few blank columns to accommodate them. Poof! 

 

If you still need to clean things up (maybe you see 0s or "FALSE" if you slipped up on anything above all throughout) don't audit them and break your head -just use conditional formatting to hide any of them with a font color that matches the background - I won't tell anyone.

 

A bit of prep but much better than going through "text to columns" for each level of sub-accounts EVERY TIME you need to pull a report. 

Need to get in touch?

Contact us
Sign in for the best experience
Ask questions, get answers, and join our large community of QuickBooks users.
Sign In / Sign Up