Summary

The defining characteristic of this file type is that Quickbooks accounts and sub-accounts are separated by : (colon character). To assess general ledger activity, only the most granular level (lowest level) account level is required. In the screenshot above, “1140700 · Site development” is the most granular account level of "1140000 · Fixed Assets:1140700 · Site development", and "1141100 · Building 1" is the most granular account level of "1140000 · Fixed Assets:1141000 · Land & Buildings:1141100 · Building 1". 

Resolution

Part 1: Extract the text after the last : (colon character delimiter)

In the column after the account column (column B in this example), name it "Lowest level account", then enter the following formula in cell B2 and drag it down:

=IFERROR(RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,":","@",LEN(A2)-LEN(SUBSTITUTE(A2,":",""))),1)),A2)

• If the delimiter is not :, replace the : in the formula above with the delimiter
• The @ character was chosen as any character that is not found in the account text. If some accounts do contain the @ character, replace @ with any character that is not in the account text.

Part 2: Extract Account ID and Account Description from the lowest level account extracted above

This part follows the logic discussed in Data Formatting Guide: Ungrouping Files: ungroup and split. Please refer to that guide for more detailed explanations of the formulas.

Account ID

In the next column (column C in this example), name it "Account ID", then enter the following formula in cell C2 and drag it down:

=LEFT(B2,SEARCH(" · ",B2)-1)

• In this example, " · " (space character plus · plus another space character) separates the account number from the account description. If a different character or string separates your account number from account description, replace " · " with that character or string.

Account Description

In the next column over (column D in this example), name it "Account Description", then enter the following formula in cell D2 and drag it down:

=MID(B2,SEARCH(" · ",B2)+3,LEN(B2))

• As noted above, in this example, " · " (space character plus · plus another space character) separates the account number from the account description. If a different character or string separates your account number from account description, replace " · " with that character or string.

Anything else on your mind?

Head over to the community with your thoughts and inquiries! You can also chat with us, or, submit a request for further assistance.

Related articles

• Data Formatting Guide: Ungrouping Files: ungroup and split
• Ungroup and Split: Extracting more complex patterns
• Data Formatting Guide - Quickbooks Sub-Accounts