Summary
This article will help you format any Quickbooks file with the following account structure, where the characters coming after the last delimiter need to be extracted (in this example, " : " [the colon character]):
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".
So we need to extract the text after the last :
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.