Take a Guess to Win Windows 10 Pro Key!

# How to Separate Numbers and Text From a Cell In Excel

Date：2020-12-28

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!

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.