How to Separate Numbers and Text From a Cell In Excel

Date:2020-12-28 Author:Sandra

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.

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

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.

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

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.

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

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)))

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *