Questions? We have answers.

Data Formatting Guide: Quickbooks Sub-Accounts separated by : (extracting the text after the last occurrence of a delimiter)

Article author
Kate H
  • Updated

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)): 

mceclip0.png

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.

mceclip1.png

 

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.

mceclip2.png

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.

mceclip3.png

 


Anything else on your mind?
Log in to MindBridge to chat with us, submit a request, or reach out to your assigned Customer Success Manager.


Related Articles

 

 

 

 

Share this article on

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.