Hàm convert text to number trong excel

Excel for ungkirke.com 365 Excel for ungkirke.com 365 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel năm 2016 Excel 2016 for Mac Excel 2013 Excel Web ứng dụng Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More...Less


Numbers that are stored as text can cause unexpected results. Select the cells, và then click

to choose a convert option. Or, vì chưng the following if that button isn"t available.

Bạn đang xem: Hàm convert text to number trong excel

1. Select a column


Select a column with this problem. If you don"t want to convert the whole column, you can select one or more cells instead. Just be sure the cells you select are in the same column, otherwise this process won"t work. (See "Other ways to convert" below if you have this problem in more than one column.)

2. Click this button


The Text khổng lồ Columns button is typically used for splitting a column, but it can also be used to convert a single column of text to numbers. On the Data tab, click Text khổng lồ Columns.

3. Click Apply

The rest of the Text lớn Columns wizard steps are best for splitting a column. Since you"re just converting text in a column, you can click Apply right away, và Excel will convert the cells.

4. Mix the format


Press CTRL + 1 (or + 1 on the Mac). Then select any format.

Note: If you still see formulas that are not showing as numeric results, then you may have Show Formulas turned on. Go khổng lồ the Formulas tab & make sure Show Formulas is turned off.

1. Insert a new column


Insert a new column next to the cells with text. In this example, column E contains the text stored as numbers. Column F is the new column.

2. Use the VALUE function


In one of the cells of the new column, type =VALUE() and inside the parentheses, type a cell reference that contains text stored as numbers. In this example it"s cell E23.

3. Rest your cursor here


Now you"ll fill the cell"s formula down, into the other cells. If you"ve never done this before, here"s how to bởi it: Rest your cursor on the lower-right corner of the cell until it changes to a plus sign.

Xem thêm:

4. Click and drag down


Click & drag down to fill the formula to lớn the other cells. After that"s done, you can use this new column, or you can copy and paste these new values khổng lồ the original column. Here"s how to do that: Select the cells with the new formula. Press CTRL + C. Click the first cell of the original column. Then on the Home tab, click the arrow below Paste, và then click Paste Special > Values.

If the steps above didn"t work, you can use this method, which can be used if you"re trying to convert more than one column of text.

Select a blank cell that doesn"t have this problem, type the number 1 into it, & then press Enter.

Press CTRL + C lớn copy the cell.

Select the cells that have numbers stored as text.

On the Home tab, click Paste > Paste Special.

Click Multiply, và then click OK. Excel multiplies each cell by 1, & in doing so, converts the text khổng lồ numbers.

Press CTRL + 1 (or + 1 on the Mac). Then select any format.

Related Topics

Replace a formula with its result top ten ways lớn clean your data CLEAN function


Need more help?

Expand your skills
Explore Training
Get new features first
Join ungkirke.com Office Insiders

Was this information helpful?

Thank you! Any more feedback? (The more you tell us the more we can help.)Can you help us improve? (The more you tell us the more we can help.)
What affected your experience?
Resolved my issue
Clear instructions
Easy khổng lồ follow
No jargon
Pictures helped
Didn't match my screen
Incorrect instructions
Too technical
Not enough information
Not enough pictures
Any additional feedback? (Optional)
Submit feedback

Thank you for your feedback!

What's new
ungkirke.com Store
English (United States) © ungkirke.com 2022