Find the Last Occurrence of a Delimiter to Retrieve the Lowest Sub Account from Quickbooks in Excel

In this post, we’ll explore a formula-based approach that does not use VBA to find the last occurrence of a delimiter and use it to retrieve the lowest sub account from a Quickbooks account list in Excel.

When I was doing some research for a project I had a couple of years ago, I was surprised by the many creative ways that Excel users have approached this task. There are several well documented methods, each creative and beautiful.

This post walks through the logic of one such solution, and explores each of the functions it uses. If you just want the answer, without all of the narrative, just skip down to the end for the formula and sample workbook.

Objective

Although this technique can be applied in many situations, the illustration used in this post is that we receive an exported account list from Quickbooks, and we need to split the full account name in order to find the lowest sub account.

Quickbooks typically uses a colon (:) between accounts, as shown in the screenshot below:

20131219a

We need a single column that contains the lowest sub account. For row 11, the formula should return the account. But, for row 12, the formula should return the Design Income account. For row 15, the formula should return the Subcontracted Labor Income account. We need the lowest sub account, regardless of the number of sub accounts in any given row.

This is exactly what we will do with a sophisticated formula. But, before we do that, let’s discuss some features that offer alternative approaches.

Alternatives

There are at least two features that we could use to help with this task. One feature is text-to-columns (Data > Text to Columns), This works well when there are the same number of sub accounts in each row. If however, some accounts have 2 sub accounts, and some have 3 sub accounts, then the lowest sub account will not appear in the same column.

If you happen to be using Excel 2013, the FlashFill feature is easy to use and may be an alternative that would work well. See the FlashFill post for more info.

If this task was part of a one-time project, I would probably use FlashFill and be done. It is fast and easy to use. If however, this was a recurring-use workbook, then, I would probably consider using a formula-based approach instead.

You see, it is our preference to eliminate all manual steps from a recurring-use workbook. If a workbook will be used in future periods, we prefer to use a formula approach rather than a manual feature approach. That way, when we paste in the updated data, such as the updated account export, the formulas will automatically prepare the data for our use without any additional manual steps. We love storing our data in tables because they auto-expand and auto-fill calculated columns. So even if there are more rows than last period, the formula will be filled down to include any new rows.

In order to eliminate a manual step from a recurring-use workbook, I’ll invest a significant amount of time trying to get the formula dialed in. This upfront investment pays handsome efficiency dividends each subsequent period. The approach presented in this post is one such investment. It may take longer initially than using FlashFill, but the benefit is that each subsequent period you can just paste-and-go.

Approach

There are several great ways to accomplish this task using formulas. I had trouble figuring out who should get credit for the idea used below since this approach is presented in many web pages. But whoever you are, your approach is brilliant…thank you!

We want to set up a formula that retrieves the lowest sub account. If there were only one delimiter in a row, such as in row 12 above, then, this task is super easy. However, when there is more than one delimiter, such as in rows 15 and 16, this task becomes more difficult. When you are trying to write a consistent formula that you can fill down throughout a range, and you want it to work on cells with no delimiter (e.g., row 11), cells with one delimiter (e.g., row 12), cells with two delimiters (e.g., row 15), and cells with any number of delimiters, then, the task suddenly becomes challenging.

If finding a single delimiter is easy, but finding the last delimiter when there are multiple delimiters is hard, we can simplify our life by replacing the last (right-most) delimiter with a unique character.

To illustrate this idea, let’s simplify our account names for a moment to make it easier to visualize. Here is a full account name as it comes to us in the export:

SegmentA : SegmentB : SegmentC : SegmentD

Using built-in Excel functions, it is hard to retrieve SegmentD because there are multiple delimiters (:). But, if we had a way to somehow identify or tag the last delimiter, and replace it with a unique character, such as $, then our job is easy. Ideally, we could convert the full account to something like this:

SegmentA : SegmentB : SegmentC $ SegmentD

Now we could ask Excel to find the tag ($) and then retrieve the values to the right of it.

This is essentially what our formula will accomplish. Before we write the formula, let’s take a moment to explore the functions it will use.

Functions

The functions we will need in our formula are:

  • LEN – returns the number of characters in a text string
  • SUBSTITUTE – replaces one character for another character
  • RIGHT – returns characters from the right of a text string
  • FIND – returns the position number of a character in a text string; error if not found
  • IFERROR – replaces an error with a specified value

Let’s take them in the order that we’ll need them. As you read through these steps, remember, the ultimate goal is to identify the final delimiter, replace it with a unique tag, and then retrieve the characters that lie to the right of it.

Step 1: Compute the Number of Delimiters

Our first task is to figure out how many delimiters (:) appear within the full account. For example, we need to return 3 for the following account:

SegmentA:SegmentB:SegmentC:SegmentD

If we could somehow count the number of characters in the full account, and then subtract from that the number of characters in a text string that excluded the delimiters, then we would have it.

For example, if we could count the number of characters in the text string above, and then subtract from that the number of characters in the text string below, we would have it:

SegmentASegmentBSegmentCSegmentD

In order to accomplish this we first need the LEN function. The LEN function returns the length of a text string, which basically means it counts the number of characters.

Assuming cell A1 contained the full account (SegmentA:SegmentB:SegmentC:SegmentD), the following function would return the number of characters, 35:

=LEN(A1)

Now that we have the total length, we need to subtract from it the length of a text string that has no delimiters. The function we’ll need to accomplish this is the SUBSTITUTE function. This function returns a text string after having replaced selected characters with desired characters.

For example, we could use the function to replace every occurrence of the delimiter with any other character. Or, we could replace every delimiter with an empty text string, essentially removing all delimiters from the result. Assuming our full account is in A1, we could return SegmentASegmentBSegmentCSegmentD by using the following:

=SUBSTITUTE(A1,":","")

This function will find each occurrence of the delimiter (:), and replace it with an empty text string “”, effectively removing all delimiters.

Back to the task at hand, we compute the number of delimiters in A1 by taking the length of A1 and subtracting the length of a text string that has the delimiters removed. This is accomplished by using the following:

=LEN(A1)-LEN(SUBSTITUTE(A1,":",""))

The formula takes the length of A1, which is 35, and subtracts from it the length of a text string without delimiters, or 32, which leaves the number of delimiters, 3.

Now that we have the total number of delimiters in the account, we can move to the next step, replacing the last delimiter with a unique character.

Step 2: Replace Final Delimiter with Unique Character Tag

We need to replace the final delimiter with a unique character that we can later use as a marker, so that we can retrieve the characters to the right of it.

Can you think of a function that we can use to substitute one character for another? Yes, the SUBSTITUTE function. However, one little detail. The SUBSTITUTE function as used above replaces all occurrences of the matching character. The SUBSTITUTE function has an optional fourth argument however, that enables us to replace one specific occurrence of the character. The fourth argument, known as instance_num, allows us to replace just the final delimiter with our unique character.

In order for us to know the instance number of the final delimiter, we need to know how many there are. Fortunately, we determined how many delimiters there are in the previous step. If we discovered that there are 3 delimiters in the text string, we can replace the third (final) instance with a unique character, such as $, by using the following formula:

=SUBSTITUTE(A1,":","$",3)

Rather than replacing all delimiters, this function only replaces the third instance or occurrence of the delimiter, and returns the following text string:

SegmentA:SegmentB:SegmentC$SegmentD

Since we want to fill the formula down, we’ll need to replace the integer 3 with the underlying functions that compute it, as follows:

=SUBSTITUTE(A1,":","$",LEN(A1)-LEN(SUBSTITUTE(A1,":","")))

So far so good? OK, let’s move to the next step.

 Step 3: Right

Now that we have tagged the final delimiter with a unique character, we can simply pull the values that lie to the right of it.

We can use the RIGHT function to retrieve characters from the right side of a text string. The function requires us to tell it the number of right-most characters to retrieve. The text string we have built thus far is:

SegmentA:SegmentB:SegmentC$SegmentD

We want to use the RIGHT function to retrieve SegmentD, so, we could use the following:

=RIGHT(A1,8)

This would return the right 8 characters from the string. Since account names have a variable number of characters, we can’t use an integer value for the second argument, 8. We’ll instead need to use functions to compute the number of characters.

Fortunately, we have tagged the position with a unique character ($) that we can use to determine the number of characters in the account name. To compute the number of characters that lie to the right of the tag, we compute the total number of characters in the text string and then subtract the position number of the tag.

We already know how to compute the total number of characters in a text string using the LEN function, so, let’s figure out how to determine the position number of the tag.

We can use the FIND function to return the position number of our unique character tag $. Let’s assume for a moment that we stored the following value in cell A1:

SegmentA:SegmentB:SegmentC$SegmentD

We could compute the position of the tag by using the following function:

=FIND("$",A1)

This formula returns 27, telling us that the tag $ appears as the 27th character within the text string.

If we subtracted the total length, 35, from the tag position, 27, then we are left with the number of characters to the right of the tag, 8.

Thus, we compute the number of characters to the right of the tag by using the following:

=LEN(A1)-FIND("$",A1)

Now that we know how many characters are in the account name, we can use the RIGHT function to return it:

=RIGHT(A1,LEN(A1)-FIND("$",A1)

This will return the characters to the right of the tag, which in our case is the account name SegmentD.

Our tagged text string is not stored in A1, it has been calculated, and thus the actual formula we need, which combines all of the steps thus far, follows:

=RIGHT(A1,LEN(A1)-FIND("$",SUBSTITUTE(A1,":","$",LEN(A1)-LEN(SUBSTITUTE(A1,":","")))))

Step 4: Error Trap

In the case where a delimiter is not found, the FIND function will return an error. When no delimiter is found in the cell, we want to return the original cell value rather than an error. Thus, we’ll wrap an IFERROR function around the whole thing. The IFERROR function allows us to substitute a value when a function returns an error.

For example, if the FIND function can’t find a delimiter, we could substitute the original cell value for the error with the following formula:

=IFERROR(FIND("$",A1),A1)

Wrapping an IFERROR function around our formula results in the following:

=IFERROR(RIGHT(A1,LEN(A1)-FIND("$",SUBSTITUTE(A1,":","$",LEN(A1)-LEN(SUBSTITUTE(A1,":",""))))),A1)

This final formula works to retrieve the values to the right of the last occurrence of the delimiter. In a way, it is like performing a right-to-left search. But, since Excel’s FIND function doesn’t support searching from the right, we use the above formula instead.

When we use a similar formula with our Quickbooks account list, we are able to populate a column that contains the lowest sub account, as displayed below:

20131219b

For more detailed information on the functions or their arguments, please consult the Excel help system.

Account numbers only?

Could we pull the account numbers into their own column? Sure, now that we have a column that is the lowest sub account, we could split that into account number and name columns with a variety of different approaches.

We could use the LEFT function to retrieve the left 5 characters. This of course assumes all account numbers are 5 characters. If they are variable length, we would need to rely on a delimiter and use the FIND function.

Since the LEFT function returns a text string, we could wrap the VALUE function around it to convert it to a numeric value if needed.

We could use the RIGHT function along with the LEN function to pull the account names into their own column.

Other Considerations

Although Quickbooks uses the colon delimiter, this formula could be tweaked to be made more universal by setting up a cell that stores the delimiter character, and then naming it. Similarly, we could place the tag character in a cell and name it too. If we named the delimiter cell d, and the tag character cell tag, the updated formula would be:

=IFERROR(RIGHT(A1,LEN(A1)-FIND(tag,SUBSTITUTE(A1,d,tag,LEN(A1)-LEN(SUBSTITUTE(A1,d,""))))),A1)

 

Conclusion

The approach presented in this post certainly uses a sophisticated formula. Figuring out and implementing a formula like this makes sense for recurring-use workbooks since you’ll be able to improve your productivity in future periods.

The sample workbook provided below includes a working formula for reference:

LowestAccount

If you have an alternative approach that you prefer, please post it in the comments below, I’d love to hear about it!

This article was written by Jeff Lenning

30 comments:

  1. Emmanuel
    Reply

    Regarding your “Find the Last Occurrence of a Delimiter to Retrieve the Lowest Sub Account from Quickbooks in Excel” post how would I extract the details from the Left of the delimiter using the code?

    Thanks

    1. jefflenning Post author
      Reply

      Emmanuel,

      Fortunately, retrieving the characters left of the delimiter is far less complicated. You would use the LEFT function and then nest the FIND function to locate the delimiter. For example:
      =LEFT(A1,FIND(“:”,A1,1)-1)

      If some of the rows have no delimiter, then you’d want to include the IFERROR function as follows:
      =LEFT(A1,IFERROR(FIND(“:”,A1,1)-1,LEN(A1)))

      Hope it helps!

      Thanks
      Jeff

  2. Hemant
    Reply

    What if I want to find the string after last of multiple delimiters? Is there any simple and sophisticated way to accomplish it?

    1. jefflenning Post author
      Reply

      Hemant,
      This post uses data with multiple delimiters, and the technique presented indeed retrieves the string after the last delimiter. The data in the Objective screenshot has row 11 with no delimiter, rows 12 through 14 with a single delimiter, and rows 15 and 16 with multiple delimiters.
      I hope this is the information you were seeking…thanks!
      Thanks
      Jeff

  3. Hemant
    Reply

    Why we shouldn’t simply use search formula as it reads the string left to right?

    A6 = HE:M:S:A:NT
    Formula: RIGHT(A6,SEARCH(“:”,A6)-1)

    Result:NT

    1. jefflenning Post author
      Reply

      Hemant,
      The FIND and SEARCH functions read the string from left to right, and therefore, returns the position of the first delimiter. When only one delimiter exists, then, you would be fine. However, when there are multiple delimiters, we have a problem because we can’t make the functions search from right to left, and thus, the position of the first delimiter is returned even when multiple delimiters exist. The formula you reference RIGHT(A6,SEARCH(“:”,A6)-1) happens to work on your sample data HE:M:S:A:NT because coincidentally there are the same number of characters (2) after the last delimiter as before the first delimiter.
      I hope this is the information you were seeking…thanks!
      Thanks
      Jeff

  4. Rich K
    Reply

    I feel I have to leave a comment as this formula was exactly what I was searching for. I had invested about three hours trying to solve the problem on my own, and I got pretty close, too. But, thanks to Jeff and his fabulous explanation, I now have a working formula returning the correct values. Thank-you, Jeff, you have explained everything beautifully.

    1. jefflenning Post author
      Reply

      Rich – thanks for taking the time to post back…I’m glad the formula was able to help!
      Thanks
      Jeff

  5. Stephen Digby
    Reply

    Thanks so much for taking the time to work though this clever formula.

  6. Clayton S
    Reply

    Jeff, your explanation really helped out a lot with the problem I am currently working on. However, I do have one question. If I wanted to find the string of SegmentB or SegmentC, how would you find it? Looking through your work, I was thinking having 2 delimiters would be the way to go, but I have not been able to figure it out.

    Thanks.

    1. jefflenning Post author
      Reply

      Clayton,
      A few ideas come to mind. If you want to stick with a formula-based approach to grab the second segment, you can manually set the Occurrence argument of the SUBSTITUTE function to 2 and then grab the MID of the values between the first delimiter and the substituted marker. However, at that point the text-to-columns or Autofill feature may be an easier option. I think I recall seeing some posts online that provide a formula-based workbook that basically does text-to-columns, so if the data is updated frequently that may be worth exploring as well.
      Thanks,
      Jeff

  7. Appu
    Reply

    Great buildup of a complex solution…….. it would be nice if excel has a reverse formula

    1. jefflenning Post author
      Reply

      Appu – I totally agree…or at least an argument that allows us to specify search in reverse!
      Thanks,
      Jeff

  8. Alan
    Reply

    Another use for the formula is to isolate the file type/extension from the full text of a file name.
    I often get a Command prompt DIR /s listing of directory and file names and it is useful to separate out the file type. That is more complicated now as file extensions may not be just 3 characters, and dots can appear inside the filename text as well, so searching for the last ‘dot’ is a key to that problem too.

    1. jefflenning Post author
      Reply

      Alan,
      Great tip…thanks for sharing!
      Thanks
      Jeff

  9. Michael
    Reply

    Hi Jeff,

    Similar to the 1st question, but could this be achieved if there are multiple characters along the string?
    ie, how could we retrieve the left part of a string until the last occurrence of a specific delimiter… for example:

    in an url string: http://hostname/path/relative/to/image/file.jpg

    Is there an excel expression to parse by ‘/’ and retrieve ‘http://hostname/path/relative/to/image’?

    Many thanks,
    Michael

    1. jefflenning Post author
      Reply

      Michael,

      Yes, indeed. The general idea is to use the LEFT function to return the string to the left of the last delimiter (rather than the RIGHT function).

      For example, instead of using the presented formula which returns the text to the right of the delimiter:
      =RIGHT([@[Full Account]],LEN([@[Full Account]])-FIND(tag,SUBSTITUTE([@[Full Account]],d,tag,LEN([@[Full Account]])-LEN(SUBSTITUTE([@[Full Account]],d,””)))))

      You would change the RIGHT function to a LEFT function, and then change the second argument so that instead of using LEN-FIND you use FIND-1, like this:
      =LEFT([@[Full Account]],FIND(tag,SUBSTITUTE([@[Full Account]],d,tag,LEN([@[Full Account]])-LEN(SUBSTITUTE([@[Full Account]],d,””))))-1)

      Hope this helps!

      Thanks
      Jeff

      1. Michael
        Reply

        It did help indeed Jeff!, many thanks for having a look… much appreciated!

        1. jefflenning Post author
          Reply

          Excellent 🙂

  10. Muhammad Waqas Bari
    Reply

    now i get all the results in proper order. thanks for your help 🙂

  11. Muhammad Waqas Bari
    Reply

    Dear Sir
    i have the data of “locations” and “dates” in the same column. i want to store locations and dates in the separate column’ but the problem is that location is missing in most cells. by using your explanation, i succeeded to separate the columns in which delimiter is used but can’t separate the complete data according to requirement. My input data is shown below:

    Oct 26 2011
    Sep 11 2011
    Helensvale Qld; Aug 27 2011
    Aug 17 2011
    Aug 06 2011
    Geelong Vic; Jul 16 2011
    Jul 11 2011
    Piara Waters WA; May 12 2011
    Apr 13 2011
    Perth WA; Feb 07 2011
    Sep 16 2010
    Melbourne Victoria; Aug 29 2010
    Jul 12 2010
    Melbourne Victoria; Jun 20 2010
    Jun 06 2010
    Mar 31 2010

    by using the formula explained by you, i got the following results
    location | date
    Oct 26 2011 | Oct 26 2011
    Sep 11 2011 | Sep 11 2011
    Helensvale Qld | Aug 27 2011
    Aug 17 2011 | Aug 17 2011
    Aug 06 2011 | Aug 06 2011
    Geelong Vic | Jul 16 2011
    Jul 11 2011 | Jul 11 2011
    Piara Waters WA | May 12 2011
    Apr 13 2011 | Apr 13 2011
    Perth WA | Feb 07 2011
    Sep 16 2010 | Sep 16 2010
    Melbourne Victoria | Aug 29 2010
    Jul 12 2010 | Jul 12 2010
    Melbourne Victoria | Jun 20 2010
    Jun 06 2010 | Jun 06 2010
    Mar 31 2010 | Mar 31 2010

    but I want the output like this
    locations | dates
    ————–| Oct 26 2011
    ————–| Sep 11 2011
    Helensvale Qld | Aug 27 2011
    ————–| Aug 17 2011
    ————–| Aug 06 2011
    Geelong Vic | Jul 16 2011
    ————–| Jul 11 2011
    Piara Waters WA | May 12 2011
    ————–| Apr 13 2011
    Perth WA | Feb 07 2011
    ————–| Sep 16 2010
    Melbourne Victori | Aug 29 2010
    ————–| Jul 12 2010
    Melbourne Victoria | Jun 20 2010
    ————–| Jun 06 2010
    ————–| Mar 31 2010

    consider pipes as cell partition and dashes as empty cells
    please help me. thanks in advance

    1. jefflenning Post author
      Reply

      Hi Muhammad,
      It looks like your results are stored in two columns, and it appears that the right column is working as expected. Thus, I would recommend using an IFERROR function for your left column. Assuming your first data is stored in A1, then something like this should work:
      =IFERROR(LEFT(A1,FIND(“;”,A1)-1),””)
      It basically says if you can’t find the delimiter then return an empty string “”.
      Hope it helps!
      Thanks
      Jeff

  12. rjohn
    Reply

    Wow, did this article save me in a pinch! Thank you so much for explaining this so well. I received a terrible spreadsheet from a client which included the street and city address in one column (almost 1,000 rows). There were many commas in some cells, so I had no idea how to tackle this until finding your post. Saved me hours!

    1. jefflenning Post author
      Reply

      Great…glad it helps…thanks for the comment!
      Thanks
      Jeff

  13. Abdullah Eyles
    Reply

    Great formula, just what I was looking for to extract filenames from Windows “path” strings. I just replaced : with \ and Bob’s your uncle!

    Thanks very much, especially for the step-by-step explanation.

  14. Sujay
    Reply

    Great Formulae helped me a lot 🙂

  15. kumar
    Reply

    Brilliant! And very well explained. Simplified my work – Thanks a ton!

    1. Kurt LeBlanc
      Reply

      Glad it helped:)

      Kurt LeBlanc

  16. megha
    Reply

    I have filepath in A6 as “D:\Kelloggs\incident-06Jul.xlsx”, i need to extract “incident-06Jul.xlsx” but the formula given above
    RIGHT(A6,SEARCH(“\”,A6)-1)
    is not working for me. What could be reason?

  17. John
    Reply

    This was one heck of an article! Thanks so very much, Jeff!

Leave a Reply

Your email address will not be published. Required fields are marked *