Background

Sometimes Excel ends up with text in a cell, when you really want a number. There are many reasons this might happen, and many ways to fix. This article describes a formula-based approach convert text values to numbers.

How this formula works

In this example, the values in column A are "stored as text". This means if you try to SUM column A, you'll get a result of zero.

The VALUE function will try to "coerce" a number stored as text to a true number. In simple cases, it will just work and you'll get a numeric result. If it doesn't work, you'll get a #VALUE error.

Add zero instead

Another common trick is to simply add zero to the text value to convert. This forces Excel to try and convert the text value to a number to handle the math operation. This has the same functionality as VALUE. Cell C7 uses this formula.

Stripping other characters

If a cell contains non-numeric characters like dashes, punctuation, and so on, you'll need to apply more effort.

The formulas in C8 and C9 show how to use the LEFT and RIGHT functions to strip non-numeric characters from a value before it's converted to a number. You can also use the MID function in more complicated situations.

If you need to strip extra spaces or other non-printing characters, see the TRIM and CLEAN functions.

Finally, the SUBSTITUTE function will let you strip other characters that appear in random locations inside a value.

To convert numbers into text values, you can use the TEXT function. In the example shown, the formula in C5 is: = TEXT ( B5 , "0" ) Background Normally, you want to maintain numeric values in Excel, because they can be used in formulas...

One way to clean up and reformat telephone numbers is to strip out all extraneous characters, then apply Excel's built-in telephone number format. This formula above uses a series of nested SUBSTITUTE functions to strip out spaces, hyphens, periods...

The Excel VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. Normally, the VALUE function is not needed in Excel, because Excel automatically converts text to numeric...

Combining functions is a core skill you *must* have in order to move beyond the basics in Excel. In this video, we show how to split, manipulate, and reassemble values using multiple functions in a single formula.

Excel Formula Training

Make sure you have a solid foundation in Excel formulas! In this step-by-step training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access so you can start today. See details here.

Key functions

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts. Read more.