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 ":" character.
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: Ungroup and split account numbers and descriptions. 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? Chat with us or submit a request for further assistance.