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.