Advertisements

How to Convert Text to Number in Excel in 4 Easy Ways

-

|

Introduction

There are 4 ways you can convert text to number in Excel. This guide demonstrates the 4 ways you can convert texts to number formats.

If you format cells with numbers as Text, it can cause unexpected results. One of the problems you will experience with numbers formatted as text is that you will not be able to sum the numbers. The cell will also display an error message.

excel convert text to number - numbers formatted as text throw errors and cannot be sumed

To resolve the error, you have to convert the text to number.

Advertisements


How to Convert Text to Number in Excel

In Excel, you can convert text to number format in 4 ways:

  1. From the Error Message
  2. Using Text to Columns
  3. Formula Method
  4. Paste Special and Multiply

Method 1: Convert Text to Number in Excel From the error Message

  • Select all the cells with the error message.
Convert Text to Number in Excel From the error Message
  • Then click on the error message and select Convert to Number.
Convert Text to Number in Excel From the error Message
The text format have been successfully converted to numbers. There is just one problem, the SUM cell was not converted. See the solution beneath the image.

To convert the SUM cell:

  • Right-click the cell and click Format Cell.
  • When the Format Cells option opens, select Number. Then click Ok.
  • Next, double-click the SUM cell and press Enter key on your keyboard.
Error message gone!

Method 2: Convert Text to Number in Excel Using Text to Columns

To use this method:

  • Click on top of the column to select the entire column.
If you do not select the whole column, this method may not work.

  • Next, click the Data tab. Then click Data Tools. Finally, click Text to Columns. The Convert Text to column wizard will open.
  • Click Finish. The format have been converted. See the second image below.
Note
You may have to SUM the numbers again and convert the SUM cell as I demonstrated earlier in this guide.

Method 3: Convert Text to Number in Excel Using Formula Method

You can also use the VALUE() function to convert text to number in Excel.

Here is how:

  • On a new cell, enter the following formula =VALUE(). Within the (), enter the cell you wish to convert. Then press Enter key.
Method 3: Convert Text to Number in Excel Using Formula Method
Cell C2 has the converted version of cell B2
Convert Text to Number in Excel Using Formula Method - =VALUE() function
  • To replicate the =VALUE() formula to the other cells, click on C2, then move your mouse towards the bottom right edge of the clicked cell. When the + sign turns black, drag the formula to all the cells you wish to convert.
Convert Text to Number in Excel Using Formula Method - drag =VALUE() formula to the other cells
All values now converted from text to number.

Method 4: Convert Text to Number in Excel Using Paste Special and Multiply

  • Enter the number, 1 into a blank cell that does not have the number problem. Select the blank cell with the number, 1. Then Press Ctrl + C on your keyboard to copy the cell. Next, select the cells that have the numbers stored as text.
  • Then right-click the selected cells and click Paste Special.
  • On the Paste Special options page, select Multiply and click Ok.
The errors are gone!
In the last method Excel multiplies each cell by 1. By multiplying the cells by 1, it converts the text to numbers.

Conclusion

I hope you were able to resolve your Excel error using one of the methods described above. If you have question or comment use the “Leave a Reply” form at the end of the page.

Alternatively you can share how you converted text to numbers and fixed this problem. This will benefit other readers.

Other Helpful Guides

Additional Resources and References


LEAVE A REPLY

Please enter your comment!
Please enter your name here

LATEST DEALS

Exclusive Student Offer_Save 10% on selected Surface devices

FEATURED POSTS

How to Change Administrator Name in Windows 10 (4 Methods)

How to Change Administrator Name in Windows 10

Introduction This guide demos how to change administrator name in Windows 10. There could be...
How to Setup WhatsApp Auto Reply in an Android Phone

How to Setup WhatsApp Auto Reply in an Android Phone

Introduction This guide demos how to setup Whatsapp auto reply in an Android phone. Steps...
How to Set Out of Office Auto Reply in Outlook, Outlook.com and Gmail

How to Set Out of Office Auto Reply in Outlook, Outlook.com and Gmail

Introduction This guide demos how to set out of office auto reply in Outlook, Outlook.com and Gmail.
How to Take Ownership of Folder in Windows 10

How to Take Ownership of Folder in Windows 10 (2 Methods)

Introduction This guide demos 2 methods to take ownership of folder in Windows 10. Options to...
How to Install Windows Server 2019 from USB

How to Install Windows Server 2019 from USB

Introduction This guide demos how to install Windows Server 2019 from USB. Steps to Install...

ADVERTISEMENTS

TRENDING POSTS

Remote Desktop Connection

Remote Desktop Connection an Internal Error Has Occurred [Fixed]

Introduction I recently received the error message "Remote Desktop Connection an Internal Error Has Occurred". It was strange because...

Find My Samsung: Register and Use Samsung Find my Mobile

Introduction Ever wondered how you could find your Samsung phone if you lost it? Find my Samsung or Samsung...
What is the Difference Between PowerShell and CMD?

Windows Powershell vs CMD: Differences and Similarities

Introduction This short guide compares Windows PowerShell vs CMD (Windows command prompt). I will cover the history and nature...
Spotify No Longer Supports this Version of Microsoft Edge

Spotify No Longer Supports this Version of Microsoft Edge [Fixed]

Introduction When you open Spotify web player on Microsoft Edge, you may receive the error message "Spotify No Longer...
Windows 10 Won't Boot

Windows 10 Won’t Boot With Black Screen? 3 Ways to Fix It

Why Won't Windows 10 Boot Up? If your Windows 10 stops with a black screen, the first question in...

BEST OF ITECHGUIDES

powershell replace

PowerShell Replace “Method” and “Operator”: Syntax, Examples

Introduction PowerShell Replace can be used to replace character strings, texts or special characters. PowerShell has a Replace...
DISM error 50: dism doesn't support servicing windows PE with the /online option

How to Fix DISM Error 50 in Windows 10 and Windows Server 2016

Introduction Dis you received DISM error 50: "DISM doesn't support servicing windows PE with the /online option"? This is...
Wifi doesn't have a valid IP configuration

How to Fix “Wifi Doesn’t Have a Valid IP Configuration” Error

Introduction "Wifi doesn't have a valid IP configuration" error is common among Windows users. When you receive this error...
How to Stop Dropbox Notifications in Windows 10 and Dropbox.com

How to Stop Dropbox Notifications in Windows 10 and Dropbox.com

Introduction This guide demos how to stop Dropbox notifications in Windows 10 and on Dropbox.com. Options...
windows safe mode

2 Easy Ways to Start Windows 10 Safe Mode

Introduction In previous versions of Windows you could start safe mode by pressing F8. But in Windows 10, F8...

RECENT POSTS

C:\G-Drive\Work Tools\Products Portal\1. New Business\2. Content Sites\1. iTechGuides.com\Posts\1. HOW TO\Microsoft\MS Office\Microsoft Word\how to add more rows to a table in word

How to Add More Rows to a Table in Word and Google Docs

Introduction This guide demos how to add more rows to a table in Word. It also has a...
How to Create a Bar Chart in Excel and Google Sheets

How to Create a Bar Chart in Excel and Google Sheets

Introduction This guide demos how to create a bar chart in Excel and Google Sheets. A bar chart is...
How to Change Administrator Name in Windows 10 (4 Methods)

How to Change Administrator Name in Windows 10

Introduction This guide demos how to change administrator name in Windows 10. There could be...
How to Create a Pie Chart in Excel and Google Sheets

How to Create a Pie Chart in Excel and Google Sheets

Introduction This guide demos how to create a pie chart in Excel and Google Sheets. A pie chart is...

iTechGuides’ Top 10 Online Games

The gaming world is getting bigger day by day. Many people are getting engaged to play online games worldwide.

How to Open Local Security Policy in Windows 10 (5 Methods)

Introduction This guide demos how to open local security policy in Windows 10. Options to...

How to Change Network from Public to Private in Windows 10

Introduction This guide demos 2 methods to change network from public to private in Windows 10.
How to Disable Touchpad in Windows 10 for Dell or HP Laptop

How to Disable Touchpad in Windows 10 for Dell or HP Laptop

Introduction This guide demos how to Disable Touchpad in Windows 10 for a Dell or HP laptop.

How to Change Account Picture in Windows 10 (2 Methods)

Introduction This guide demos 2 methods to change account picture in Windows 10. Windows 10...
How to Create Xbox Live Account

How to Create Xbox Live Account (2 Methods)

Introduction This guide demos 2 methods to create Xbox Live account. Options to Create Xbox...

ADVERTISEMENTS

MUST READ

foreach in file powershell

How to Iterate the Content of a Text File in Powershell in 2 Easy...

Introduction If you have a text file with data you wish to use, you can use PowerShell Get-Content ...

Change User Name and Rename User Folder in Windows 10

Introduction There are so many reasons why you may want to change user name in Windows 10 or rename...

How to Change Account Picture in Windows 10 (2 Methods)

Introduction This guide demos 2 methods to change account picture in Windows 10. Windows 10...
how to merge cells in excel

How to Merge Cells in Excel in 2 Easy Ways

Introduction You can merge two Cells in Excel using CONCATENATE function or the “&” (ampersand) operator. Though Excel has...
WSCRIPT and CSCRIPT Commands: Syntax, Parameters, Examples

WSCRIPT and CSCRIPT Commands: Syntax, Parameters, Examples

Introduction WSCRIPT is the Windows-based version of Windows Script Host while CSCRIPT is the command-prompt-based version. This guide...

LATEST DEALS

Exclusive Student Offer_Save 10% on selected Surface devices

By using this website you agree to accept our Privacy Policy and Terms & Conditions