Convert Numbers Stored As Text To Numbers

The tutorial shows many different ways lớn turn a string into lớn a number in Excel: Convert khổng lồ Number error checking option, formulas, mathematic operations, Paste Special, and more.

Sometimes values in your Excel worksheets look lượt thích numbers, but they don"t add up, don"t multiply and produce errors in formulas. A comtháng reason for this is numbers formatted as text. In many cases Microsoft Excel is smart enough lớn convert numerical strings imported from other programs to lớn numbers automatically. But sometimes numbers are left formatted as text causing multiple issues in your spreadsheets. This tutorial will teach you how khổng lồ convert strings to "true" numbers.

Bạn đang xem: Convert numbers stored as text to numbers

How to convert text lớn number in Excel

How lớn identify numbers formatted as text in Excel

Excel has an inbuilt error checking feature that alerts you about possible problems with cell values. This appears as a small green triangle in the top left corner of a cell. Selecting a cell with an error indicator displays a caution sign with the yellow exclamation point (please see the screenshot below). Put the mouse pointer over the sign, và Excel will inkhung you about the potential issue: The number in this cell is formatted as text or preceded by an apostrophe.

*

In some cases, an error indicator does not show up for numbers formatted as text. But there are other visual indicators of text-numbers:

NumbersStrings (text values)
Right-aligned by default.If several cells are selected, the Status Bar shows Average, Count and SUM.Left-aligned by mặc định.If several cells are selected, the Status Bar only shows Count.The Number Format box displays the Text format (in many cases, but not always).There may be a leading apostrophe visible in the formula bar.

In the image below, you can see the text representations of numbers on the right & actual numbers on the left:

*

How to convert text to number in Excel

There are a handful of different ways to change text khổng lồ number of Excel. Below we will cover them all beginning with the fastest & easiest ones. If the easy techniques don"t work for you, please don"t get disheartened. There is no challenge that cannot be overcome. You will just have sầu lớn try other ways.

Convert to number in Excel with error checking

If your cells display an error indicator (green triangle in the top left corner), converting text strings to numbers is a two-clichồng thing:

Select all the cells containing numbers formatted as text.Cliông xã the warning sign và select Convert khổng lồ Number.

Done!

*

Convert text into number by changing the cell format

Another quichồng way to lớn convert numerical values formatted as text to lớn numbers is this:

Select the cells with text-formatted numbers.On the Home tab, in the Number group, choose General or Number from the Number Format drop-down list.

*


chú ý.
This method does not work in some scenarios. For example, if you apply the Text format khổng lồ a cell, enter a number, and then change the cell format khổng lồ Number, the cell will remain formatted as text.

Change text to number with Paste Special

Compared to the previous techniques, this method of converting text khổng lồ number requires a few more steps, but works almost 100% of time.

To fix numbers formatted as text with Paste Special, here"s what you do:

Select the text-number cells và set their format lớn General as explained above.Copy a blank cell. For this, either select a cell and press Ctrl + C or right-cliông xã và choose Copy from the context thực đơn.Select the cells you want to convert to numbers, right-cliông xã, and then click Paste Special. Alternatively, press the Ctrl + Alt + V shortcut.In the Paste Special dialog box, select Values in the Paste section & Add in the Operation section.Click OK.

*

If done correctly, your values will change the mặc định alignment from left khổng lồ right, meaning Excel now perceives them as numbers.

Convert string to lớn number with Text to Columns

It is another formula-miễn phí way to lớn convert text khổng lồ number in Excel. When used for other purposes, for example to split cells, the Text khổng lồ Columns wizard is a multi-step process. To perkhung the text lớn number conversion, you click the Finish button in the very first step :)

Select the cells you"d lượt thích to convert to lớn numbers, & make sure their format is mix to lớn General.Switch to lớn the Data tab, Data Tools group, và click the Text to Columns button.In step 1 of the Convert Text to lớn Columns Wizard, select Delimited under Original data type, và cliông xã Finish.

That"s all there is to it!

*

Convert text to lớn number with a formula

So far, we have sầu discussed the built-in features that can be used khổng lồ change text to number in Excel. In many situations, a conversion can be done even faster by using a formula.

Xem thêm:

Formula 1. Convert string khổng lồ number in Excel

Microsoft Excel has a special function lớn convert a string lớn number - the VALUE function. The function accepts both a text string enclosed in quotation marks and a reference lớn a cell containing the text to be converted.

The VALUE function can even recognize a number surrounded by some "extra" characters - it"s what none of the previous methods can bởi vì.

For example, a VALUE formula recognizes a number typed with a currency symbol and a thousvà separator:

=VALUE("$1,000")

=VALUE(A2)

To convert a column of text values, you enter the formula in the first cell, và drag the fill handle to copy the formula down the column:

*

For more information, please see VALUE formula lớn convert text to number.

Formula 2. Convert string to lớn date

Apart from text-numbers, the VALUE function can also convert dates represented by text strings.

For example:

=VALUE("1-Jan-2018")

Or

=VALUE(A2)

Where A2 contains a text-date.

By mặc định, a VALUE formula returns a serial number representing the date in the internal Excel system. For the result to lớn appear as an actual date, you just have to lớn apply the Date format lớn the formula cell.

*

The same result can be achieved by using the DATEVALUE function:

=DATEVALUE(A2)

For more information, please see How to convert text to lớn date in Excel.

Formula 3. Extract number from string

The VALUE function also comes in handy when you extract a number from a text string by using one of the Text functions such as LEFT, RIGHT & MID.

For example, khổng lồ get the last 3 characters from a text string in A2 and return the result as a number, use this formula:

=VALUE(RIGHT(A2,3))

The screenshot below shows our convert text to number formula in action:

*

If you don"t wrap the RIGHT function into VALUE, the result will be returned as text, more precisely a numeric string, which makes any calculations with the extracted values impossible.

For more information, please see How khổng lồ extract number from string in Excel.

Change Excel string to lớn number with mathematic operations

One more easy way khổng lồ convert a text value khổng lồ number in Excel is khổng lồ persize a simple arithmetic operation that does not actually change the original value. What can that be? For example, adding a zero, multiplying or dividing by 1.

=A2+0

=A2*1

=A2/1

If the original values are formatted as text, Excel may automatically apply the Text format lớn the results too. You may notice that by the left-aligned numbers in the formula cells. To fix this, be sure to lớn phối the General format for the formula cells.

*


Tip.
If you"d lượt thích to lớn have the results as values, not formulas, use the Paste Special feature khổng lồ replace formulas with their values.

That"s how you convert text lớn number in Excel with formulas and built-in features. I thank you for reading và hope lớn see you on our blog next week!