Breaking apart account strings – Formula addition

Using the same example from Text to Columns.

Instead, we will use formulas to break out the different segments of the account string.

Assuming our string is in cell A2. Amount is in B2. We will put our first formula in C2.

The formula we will use is “=MID(A2,1,3)“. ┬áThe function’s arguments are in three parts. First is the text (A2). Then the starting number, which is 1, then the number of characters. We want three characters because that’s how long our fist segment is.

The result is “100”, which is our first segment. We could do this again to get the next segment. In cell D2, enter “=MID(A2,5,6)“. ┬áThe difference is we are starting at the fifth position, and we a selecting six digits.

The result is “401000”- our second segment. We could do this for the remaining three parts of the account string. So, why would we choose to do this vs Text to Column? Well, if your data is static, Text to Column is probably the best. But, you may be working with a query from a database that can be updated. In that case, formulas would be the best route.

 

Text to Columns

In our sample general ledger detail, we get the full string account number. But, we are wanting to work with the individual components of the string (fund, major, etc.). A useful tool for this is Text to Columns.

Our example shows the full string account number and the amount.

In Excel, go to “Data” tab. Highlight just the three cells containing the string. Then Text to Column button. Then choose radius button “Delimited” because we want to use the “-” character to break apart our string.

Put a “-” in the Other.

Then Next>

At this point, you are able to choose which new columns to keep. I have entered $D$3 for the new data. That way it places it to the side of my original list.

I have added the labels above our new columns.

Note- I could have chosen “Text”, instead of “General”. This would have kept the formatting of “001” vs 1.