Excel Professor

The other day I was asked if I could break out numbers from a cell that contained both text and numbers. My manager wanted the numbers in one column and the text to the right in the adjacent column.

I thought sure, easy to do with a combination of =Left =Mid and = Right functions. However, when I looked at the data I noticed that the numbers did not start in the same position in the text. I also noticed that some cells had 6 numbers while others had 4. To make things even more difficult, some cells started with numbers first.

Well I thought about the problem and decided to solve it by converting the formula into an array.

What’s an array formula?

An array formula is a formula that works with array data values rather than a single data value. 

There are two different types of array formulas

1.     Formulas that work with an array or series of data and aggregate the result. Sum, Average and Count are good examples of this type as they all return a single value to a single cell.

2.     Formulas that returns an array of numbers as its result. These array formulas are entered into multiple cells that are then treated as a group. For example, =ROW(A1:E20).

In the above example I have my original data in column A. I first use the formula in column B to extract the numbers in the string. After entering the formula, I hold down the control key, shift key and enter key all at the same time to convert the formula into an array.

Once I had that, it was a simple matter of getting the length of how many numbers were in the original data and the starting position of the numbers in the string (Columns C and D).

Based on that, I could then parse out the text to the right of the number (Column E).

Now to accomplish all this I needed to use 7 functions….

=IF

=MID

=MATCH

=ROW

=LEN

=COUNT

=ISNUMBER

All of these functions are simple to use but the trick was combining them to get my solution.

Indeed knowing what functions are available in Excel will allow you to join them to solve almost any problem you have to solve. So it’s a good idea to learn them or at least know they exist so that you can reference them.

{=1*MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$100),1)),0),COUNT(1*MID(A2,ROW($1:$100),1)))}

{=MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($2:$9),1)),0)}

{=IF(MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($2:$9),1)),0) = 1,MID(A2,(E2+D2),90),MID(A2,(E2+D2+1),90))}

How would you have solved the above problem?