A few months ago I explained how to use the Excel functions Left, Mid, or Right to to pull information from the beginning, middle, or end of another cell’s contents. That prompted this question from a Youtube viewer:
I’ve got a slightly different challenge: I need to extract numbers from a single cell which reads something like “pushups:20 situps:40”. I need to extract the 20 and the 40 into two respective columns called pushups, and situps. Unfortunately I cannot use the character count method because the source cell might contain “chinups:10 curls:30 situps:40 pushups:25” as well. In other words, the data isn’t consistently structured. Is there a way to “look for” a certain number of characters that occur “after” a specific string, like “pushups:”?
As a quick reminder, the MID function requires you to provide three pieces of information: the text or cell you want it to search in, the starting position of the text you want to retrieve, and the number of characters you want to retrieve. If his data was always laid out the same way, it would be very easy. With chinups:10 curls:30 situps:40 pushups:25 in cell A2, the formula
=MID(A2,18,2)
would give the answer 30. Excel would give you 2 characters starting with the 18th character in cell A2.
The problem in this situation is that the number he wants could be in a different location within the text string every time he looks for it. We have to find a way to locate the correct starting position each time.
You can nest the SEARCH function within MID to get your starting point. If you have chinups:10 curls:30 situps:40 pushups:25 in cell A2, the formula
=SEARCH(“curls:”,A2)
would return the answer 12 because “curls:” begins at the 12th character in the text string. From there you need to add enough characters to get to the characters you want to extract, in this case 6 (c u r l s : and a space).
So by substituting that for the number 18 in the original formula we get
=MID(A2,SEARCH(“curls:”,A2)+6,2)+0
Here “SEARCH(“curls:”,A2)+6″ finds curls with cell A2 and gets us to the right starting point for the 2 characters we want to extract.
The +0 at the end just forces Excel to see the result as a number instead of as text.
Of course you would need a different formula for each exercise with the correct text string and corresponding number of characters. If you have the name of each exercise in a column heading you could make a more universal formula by replacing the name of the exercise with the location of a column heading, and by replacing the number 6 with a formula that tells us the length of the column heading. For example, if you had CURLS: in cell C1 as a column heading, your formula could be
=MID(A2,SEARCH(C1,A2)+LEN(C1),2)+0
Changing the appropriate references from relative to absolute, like this:
=MID($A2,SEARCH(C$1,$A2)+LEN(C$1),2)+0
would let you copy the formula to adjacent columns and down to other rows without having to rewrite the formula each time.
So that solves the problem of extracting characters from a starting point that changes. But what if the number of characters we need to extract also changes?
If the number following the exercise is only 1 digit we would still be OK. Since there is a space after each number, the formula would return the single digit plus the space as the answer. Because the “+0” on the end of the formula converts the answer to a number, Excel would drop the trailing space.
But if the number can vary from 1 to 3 (or more) digits we have to add another piece to the formula. Instead of just plugging in “2” as the number of characters to return, we need a formula that will calculate the number of characters to extract. Here is how we build that formula.
Start by finding for the position of the space following the name of the exercise, “Curls:” in this case. That looks like this
SEARCH(” “,A2,SEARCH(C1,A2)
Or in plain English, search for a space in cell A2, but don’t start at the beginning of A2, start wherever you find a match for the text in cell C1. In this example that would give us 20 because the space following Curls: is the 20th character in cell A2.
Then we have to subtract the number of characters that we don’t want from the beginning of the string. We get that in two pieces:
(1)the starting point of the name of the exercise
SEARCH(C1,A2) gives 12. You’ve seen this before so I won’t explain it again.
(2) the length of the name of the exercise:
LEN(C1) gives us 6. (in English, that would be the length of the contents of cell C1, our column heading)
So 20–12–6=2
Combine all of those in place of the 2 in the original formula and you get:
=MID(A2,SEARCH(C1,A2)+LEN(C1),SEARCH(” “,A2,SEARCH(C1,A2))–SEARCH(C1,A2)–LEN(C1))+0
In addition to showing how to extract a variable length text string from a variable position within a cell, this also shows that you don’t always need to be able to sit down and write a long, complex formula from scratch. By starting with a simple formula and gradually inserting additional functions or formulas to deal with exceptions, you can solve problems that you thought were out of reach.
