How to Separate Numbers and Text From a Cell In Excel

Today, we are going to learn how to separate numbers and text from a cell in Excel. Let’s divide this tutorial into three parts below.

Ok, let’s get started.

Situation 1: Numbers are in front of Text.

This formula allows you quickly get numbers from cell B3.

=-LOOKUP(0,-LEFT(B3,ROW($1:$15)))

ROW($1:$15) indicates the maximum length of the number you want to extract, which can be adjusted according to actual needs. Take an example, if you enter ROW($1:$2), then the cell C3 would only display 12.

Situation 2: Numbers are behind Text

It’s very similar to the last situation. Just enter the formula

=-LOOKUP(0,-RIGHT(B7,ROW($1:$15))) into Cell C7.

Situation 3：Number in the Middle of the text

This situation is more complicated than the above two situations. But its general formula can actually be applied to the above two situations.

=-LOOKUP(1,-RIGHT(LEFT(B11,LOOKUP(10,–MID(B11,ROW($1:$15),1),ROW($1:$15))),ROW($1:$15)))

You can copy the above formulas to use. But if you still want to know the reason or why this code works, we will update the tutorial about the function of LOOKUP.  See you next time and Merry Christmas!