Summary

This guide is to help you understand the best ways to ungroup and split account numbers and account descriptions that appear in the same column within a file.

Resolution

Consider the following excerpt from a GL file:

The problems

1. This file is grouped by account: only one row contains account information (rows 7, 5695), followed by all transactions for that row (rows 8-5691, 5696-6176) (violates row requirement 1)
2. Account information is in column A but contains both account number and account description separated by " - " (space character plus - plus space character) (rows 7, 5695) (violates column requirement 1)
3. There are unneeded total/subtotal rows (5692-5693, 6177-6178) and blank rows under them (violates overall requirement 2)

The solution (formatting needed)

1. Ungroup by account (drag down account information to each transaction row)
2. Split account number and account description into different columns
3. Exclude extraneous total/subtotal/blank rows and extraneous rows (rows 1-4) above the header row (row 5)

We insert two columns, "Account ID" and "Account Description", before the beginning of the file to split out account number and account description and drag them down:

Account ID: Cell A7 and below

=IFERROR(LEFT(C7,SEARCH(" - ",C7)-1),A6)

• SEARCH(" - ",C7)

  finds the position in the string C7 where the first " - " string appears. We subtract 1 from that to get the number of characters the account number takes up (5 characters for account 11110),
• then we use LEFT(C7,) to get the leftmost number of characters from that account string (so the first 5 characters in cell C7).
• Finally, IFERROR(A,B) returns A if A is not an error, B if A is an error.
  • We want to extract the account number and account description only from rows with that information in column C.
  • We notice that these cells with account number and account description are identified by the presence of " - " separating account number and account description. SEARCH() returns an error (#VALUE!) if the string is not found; thus, for all the rest of the rows, #VALUE! is returned by SEARCH() (which flows up to the LEFT() formula that it is nested in. For these other rows, we just want to return the account number that we had extracted in the row above.
  • So in this example, the nested LEFT() and SEARCH() in cell A7 returns 1110, because cell C7 has " - " in it.
  • But in cell A8,

    =IFERROR(LEFT(C8,SEARCH(" - ",C8)-1),A7)

    the nested LEFT() and SEARCH() returns #VALUE! because cell C8 (2019-04-01) does not have " - " in it.
  • This is caught by IFERROR(), which, seeing the #VALUE!, moves on to A7, which is 1110 as discussed above, and thus also returns 1110. This all continues until it hits row 5695, when it finds that cell C5695 has " - " in it and therefore extracts account 1-1112

Account Description: Cell B7 and below

=IFERROR(MID(C7,SEARCH(" - ",C7)+3,LEN(C7)),B6)

This column's formulas follow the same logic as that of the Account ID column (column A), except instead of using LEFT(), it uses MID(), which is useful for extracting text from anywhere not at the beginning all the way to the end, or not to the end. The syntax is:

MID(text, start_num, num_chars)

where text is the string to search (C7)

start_num is the position to start at. We use...

SEARCH(" - ",C7)+3

...because we want to start after the " - ".

Recall from above, SEARCH(" - ",C7) gives the position where the " - " starts. Since " - " is 3 characters long, we add 3 to get the position of where the account description starts.

Finally, the LEN(C7) gives the length of the string in cell C7, to ensure that we take it all the way to the end.

Subtotals

The following are the formulas for cells G1 and H1:

=SUBTOTAL(9,G5:G50000)
=SUBTOTAL(9,H5:H50000)

We use 50000 because this particular file has under 50000 rows. Change that to a large enough number to accommodate the total number of rows your file has. We add these formulas in order to check that the file balances when we do the final step of filtering to keep only the transaction rows (next).

Filter to keep only transaction rows

After turning on filters, check column C (Date) and we see that there are the 2020 and 2019 dates, then the rest are account numbers:

Copy and paste values into a new workbook

Noting that all the dates are the transaction rows, select only the dates:

Note that results of our SUBTOTAL() formulas in cells G1 and H1 are showing that the total transactions are in balance (yay!)

Our final step is to select the header row and selected transaction rows (i.e., row 5 onward in the filtered view - you can start at cell A5, hit Ctrl-Shift-End to get to the end of the file, and adjust the position as necessary), then copy, then paste special values in a new workbook

Finally, in the new workbook, be sure to format the Date column as Date in YYYY-MM-DD format (hit Ctrl-1 on PC or ⌘1 on Mac):

And now we have a beautifully formatted file, ready to import into MindBridge!

Ungroup in Excel, split in MindBridge

MindBridge does already provide a feature during column mapping that allows the splitting of columns, so if you prefer to use a simpler formula only to ungroup, without splitting at the same time, you can just add one Account column that will pull the value from column B if it contains " - ", otherwise pull the value found above:

=IF(ISERROR(SEARCH(" - ",B7)),A6,B7)

Then proceed with the steps described above to filter, copy and paste into a new workbook.

Begin importing the formatted GL file into MindBridge, then at Step 2 for column mapping, click on the three dots to the right, and select Split column. Learn how to split columns.

More complicated account splitting

Learn how to build a formula to split out an account number.

Anything else on your mind? Chat with us or submit a request for further assistance.