Convert Text to Number

Have you ever been baffled by Excel’s handling of numbers? Are you tired of grappling with non-cooperative imported data that insists on being treated as text rather than numbers? We can relate to that, and we’re here to walk through this process with you.

Video

Step-by-step

Let’s unravel the mystery of converting text to numbers in Excel, beginning with fundamental concepts of numeric values, text values, and how cell formatting plays a role. We’ll follow this up with a detailed walkthrough of solutions for converting text to numbers, both manually and with formulas. We will walk through all of this using three illustrative exercises.

Exercise 1: Understanding Text, Numbers, and Formatting

Stored values vs Displayed values

If we type 1 into an empty cell, Excel recognizes this as a number and stores it as such. Strings such as ‘Jeff’ are stored as text. Let’s refer to these as Stored values. That it, the underlying value that is stored in the cell. Stored values are not technically the same as Displayed values, or what you see in the cell.

The difference between a Stored and Displayed value is cell formatting. In other words, the Stored value is the value that is used in formulas, whereas the Displayed value is what we see after applying some type of cell formatting. For example, a Stored value may be 12345, but the Displayed value may be $12,345.00. Formulas ignore cell formatting, and operate on the underlying stored value.

Still with me? Good, let’s tackle the next concept, data types.

Data Types

Cells can store a variety of different types of values. For example, you can type text, a number, a date, a time, a TRUE/FALSE, and so on. We’ll call these data types. Now, here is a key point: changing the formatting of a cell that contains a Stored value, does not change the data type … it only changes the Displayed value.

Be default, numbers are right-aligned in cells whereas text strings are left-aligned. However, you can change the alignment formatting of the cell so that Excel displays numbers on the right, middle, or left. This formatting change doesn’t change the Stored value.

This also means if you have a Stored value that is a number, and change the cell format to text, the data type of the Stored value is still a number. So, changing the formatting of a cell that contains a value does not change the data type of the Stored value.

Still with me? Good, let’s go to the next idea.

Formatting is a clue

If you take an empty cell, and then apply a cell format of Text, and then type in a number such as 1, Excel will actually store that as a text. In other words, Excel takes a clue from the cell formatting when it determines how to store the value. So, this is interesting right. Formatting can impact the stored value and data type. Yes. But, it depends on which is there first … the value or the formatting.

If the cell has a value, changing the format does not change the data type. But, if you format a cell first, and then type in a number, Excel uses the existing formatting as a clue for how to store the value. This is one way that a value entered as a number can be stored as a text, and interpreted as a text value in formulas. Depending on the formula, this can cause unexpected results.

For example, in a lookup function like XLOOKUP or VLOOKUP, equivalent values, when stored as different data types, do not match. This is a large and rather frustrating situation because it causes the user to wonder if the formula was written incorrectly due to the unexpected error or result. Often, the formula is just fine, but it is the underlying data (and data type mismatch) that is causing the issue.

This type of situation can also occur when importing data from exports. So, when a number is being stored as a text value, but it needs to be stored as a number, how do we change the data type?

That leads us to our next exercise.

Exercise 2: Changing Text to Numbers Manually

Let’s say we have numbers that are being stored as text values. We want to convert them to numbers, so they will be stored as a number data type. Here are three manual techniques that may help.

Excel Alerts

With certain Excel versions, an alert notifies you that your data might be stored as text when it should be stored as a number. You can click the alert to get more information or to do the conversion to a number.

But, rather than converting the cells one at a time, you can actually highlight all of the cells, and then click the alert:

Once you click Convert to Number, Excel performs the conversion and now we have numbers stored as a number data type:

So that is one option. Let’s take a look at another.

Copy Paste Add

Select any empty cell, in this case D14, and then do a standard Copy. This essentially causes Excel to save a 0 to the clipboard.

Next, select the range of cells you’d like to convert to a number.

Do, do a Paste Special and choose Add in the resulting Paste Special dialog:

This causes Excel to add 0 to the selected range. This mathematical process essentially causes Excel to change the data types to numbers.

Now let’s take a look at one more option.

Text to Columns

Start by select the range.

Then click the Data > Text to Columns command to open the wizard:

Click Finish (or you can also click Next for each step and accept all default options) and bam:

You’ll notice the cell formatting hasn’t been changed, so the numbers are left aligned. No worries, the underlying data type has been changed, but you can always change the cell formatting as desired.

Now, depending on what you are working on, you may want to do the conversion with a formula instead of manually. So let’s tackle that next.

Exercise 3: Using a Formula

The VALUE function can convert a number that is stored as a text to a number. We can point the argument to the cell, like this:

=VALUE(B12)

We can fill the formula down:

So, depending on what you are working on, using the VALUE function is another option. It can even be nested inside of other functions as needed.

Conclusion

We hope this helps you maneuver those pesky number-text issues more smoothly. If you have any alternatives, questions, or suggestions, please share by posting a comment below … thanks!

File Download

FAQ

What causes numbers to be stored as text in Excel?

• Numbers may be stored as text in Excel due to the initial formatting of a cell before the number is entered or because of discrepancies when importing data from other sources. The cell format can instruct Excel on how to treat the entered data.

How can I tell if a value in Excel is stored as text or as a number?

• You can identify if a value is stored as text or a number by checking the alignment in the cell; by default, text is left-aligned and numbers are right-aligned. Additionally, if a cell has a green triangle in the top left corner, Excel is indicating that a number is stored as text.

What is the difference between stored values and displayed values in Excel?

• Stored values are the actual data saved in Excel cells, which are used in calculations and formulas. Displayed values are how the data appears visually in the spreadsheet, which can be altered through formatting options like font style or alignment without affecting the stored data.

Can changing the format of a cell change the data type of its stored value?

• Changing the format of a cell that already contains a value does not change the data type of the stored value. However, formatting a cell before entering a value can influence how Excel interprets the data type of the input.

What are some methods to manually convert text to numbers in Excel?

• There are several manual methods to convert text to numbers in Excel, including using the Excel alert that appears with an option to convert, utilizing the ‘Paste Special’ feature with the ‘Add’ operation, and employing the ‘Text to Columns’ wizard.

How does the ‘Paste Special’ add method convert text to numbers?

• The ‘Paste Special’ with ‘Add’ method works by adding zero to the selected range, which forces Excel to recalculate and convert the text entries into numerical entries, recognizing them as numbers.

What is the ‘Text to Columns’ feature, and how does it help in data conversion?

• ‘Text to Columns’ is a feature in Excel that separates the contents of one Excel cell into separate columns based on a delimiter or fixed width. Using this feature without specifying a delimiter effectively converts text-formatted numbers into actual numeric data types.

How can the VALUE function be used to convert text to numbers?

• The VALUE function in Excel converts a text string that represents a number into a number. For instance, =VALUE(A1) would return the value in A1 as a numeric data type. This function is useful for converting cell contents when formulas are dependent on those cells.

When should I use manual methods versus formulas for converting text to numbers?

• Manual methods are best for one-time conversions or small data sets, where it’s feasible to convert each entry individually or in batches. Formulas, like using the VALUE function, are more efficient for ongoing conversions or when integrating into other formulas across large datasets.