microsoft_excel

Numbers displayed as text after LEFT, MID or RIGHT formula

Assuming that you have a range of cells containing containing both texts & numbers within the same cell. You would like to extract the numbers for some calculation, such as addition. When you apply the formulas LEFT, MID or RIGHT to the range of cells, Excel immediately present the extract numbers as text. In the example below, I have extracted numbers from Column A using formulas.

When we try to sum up the 3 numbers in (B2:B4), it returns zero. This is because Excel treated them as Text and not numbers.

Excel has a proper formula to convert such cases to Values. And being the friendly Excel we know, the name of the formula is�. VALUE. So placing the Text inside this formula will convert it to a Value. And my SUM in C5 is now able to calculate C2:C4.

You can of course put insert the Value formula directly into the cells in column B itself. Just place a VALUE formula in front of the formula we use to extract the numbers (after the equal sign) and close off the formula with a bracket at the end of the formula.

But I’m going to show you a slightly shorter method that will give you the same results. Simply add in “+0” after the formula and there you go.

Note that this method works only for cells which are not formatted as TEXT.

Convert Text-formatted numbers to proper numbers.

New! Comments

Have your say about what you just read! Leave me a comment in the box below.

Share this page:

What’s this?

Enjoy this page? Please pay it forward. Here’s how…

Would you prefer to share this page with others by linking to it?

Click on the HTML link code below.
Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.

<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>

Excel Courses for Business Professionals

How to sleep and lose weight

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.

advanced-excel.com is in no way associated with Microsoft