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

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.