Put a different way, we are essentially eliminating the first 11 characters or spaces of our original string to make sure our substring begins with the 12th character.
Because of this, we need to add 11 to the 57 we counted from the right in order to compensate. Subtracting this number from the length, or ‘LEN’, of each string gives us the exact number of characters we want to parse out. So the formula for A2 should look like “=MID(A2,12,LEN(A2)-68)”.
This same concept using ‘LEN’ works well with both the ‘LEFT’ and ‘RIGHT’ functions also when you need to parse out substrings of varying lengths (so long as the rest of the string to the right or left, respectively, are the same length across each data string).
Consider the previous example. Say you wanted to extract the same substring we parsed out but instead of starting with the street number you wanted to start at the beginning of the string.
Then your formula would simply be “=LEFT(A2,LEN(A2)-57)”.
So now you can see not only the general usefulness of the ‘LEFT’, ‘MID’, and ‘RIGHT’ functions in relatively simple scenarios, but also the real power of the functions in more complicated situations with varying string lengths across a data set.
It’s amazing how adding a simple little function like ‘LEN’ to the mix can level up your new found data parsing skills to create an effective solution to an otherwise time consuming and arduous task.