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.

Advertisement

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.
Advertisement
  • 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

FEATURED POSTS

network discovery keeps turning off server 2016

How to Fix Network Discovery If it Keeps Turning Off in Server 2016

Does network discovery keep turning off in your Windows server 2016? It is likely that one of its dependent services is not...
how to install windows 10 1903 update manually

How to Install Windows 10 1903 Update Manually

Windows 10 1903 Update was released in May, 2019. But some users are not yet offered the update via automatic update. The...
spotify web player not working

Spotify Web Player Not Working [Fixed]

Introduction Spotify Web Player may stop working for you with the following error messages: "Spotify Web Player an Error...
DISM.exe /Online /Cleanup-Image /Restorehealth

DISM.exe /Online /Cleanup-Image /Restorehealth Explained

What is DISM.EXE /Online /Cleanup-image /RestoreHealth? "DISM.exe /Online /Cleanup-Image /Restorehealth" is a DISM command that repairs issue with the...
DHCP Relay agent

DHCP Relay Agent: Configuration in Windows Server 2016

What is a DHCP Relay Agent? A DHCP Relay Agent allows DHCP clients in a different network subnet to...

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

windows update stuck featured

How to Get Windows Update to Work If Download is Stuck at 0%

Experiencing Windows Update stuck at 0%? Have you waited for hours already, yet to progress? You are not alone! A lot of...
The Requested Operation Requires Elevation

How to Fix “The Requested Operation Requires Elevation” Error

Introduction "The Requested Operation Requires Elevation" error may be caused by a number of reasons. Here are some of...
PowerShell Not Equal

PowerShell Not Equal Operator: Applications, Examples

Introduction PowerShell Not Equal Operator is one of the Comparison Operators. PowerShell Not Equal (-ne) compares two values. If...
ClearScore

ClearScore Can Help Improve Your Credit Score for FREE (Here is How)

What is ClearScore and Who is ClearScore? ClearScore (Some call it Clear Score!) is a London-based financial services company...
powershell try catch

Powershell Try Catch Finally and Error Handling

Introduction Powershell Try Catch blocks are used to handle terminating errors in PowerShell scripting. When use Powershell Try Catch...

RECENT POSTS

disable cortana windows 10 featured

How to Disable Cortana in Windows 10 (2 Methods)

Introduction Some Windows 10 users may not like Cortana. Solution? Disable Cortana. You are probably reading this because you...
bootrec /fixboot access is denied

How to Fix BootRec /FixBoot Access is Denied Error in Windows 10

Introduction BootRec /FixBoot Access is Denied Error in Windows 10? This error is likely caused by corrupt EFI directory.
Reset Windows 10

How to Reset Windows 10 (2 Methods)

Introduction If your Windows 10 is broken, one available way to fix it is to use Windows 10 reset....
system restore windows 10

How to Enable and Use System Restore in Windows 10

Introduction System restore in windows 10 is a very important tool that is probably ignored by most users. But...
windows has stopped this device code 43

How to Fix Windows Has Stopped this Device Code 43 Error

Introduction Sometimes you may receive "Windows has stopped this device code 43" error. This error is likely to come...
Windows could not automatically detect this network's proxy settings

How to Fix “Windows Could not Automatically Detect this Network’s Proxy Settings”

Introduction You suddenly lose the ability to connect to the internet. Then you run network troubleshooter and it returns...
printer offline

5 Ways to Restore Your Printer Online If Status is Offline

Introduction Is your printer offline? Setting it online is very simple but sometimes it is more than just setting...

How to Fix “BootMgr is Missing” Error in Windows 10

Introduction If you receive Fix "BootMgr is missing" Error in Windows 10, the default response is panic! But you...
windows 10 search featured

How to Fix Windows 10 Search If it Stops Working

Introduction Are you having troubles with Windows 10 search? Apparently, it is a fairly common problem with a number...
oooops, something went wrong. reload

How to Fix “Oooops something went wrong. reload” Spotify Error

Introduction If you receive "Oooops something went wrong. reload" Spotify error, do not panic. The fix is simpler than...

MUST READ

Check Disk (CHKDSK) Windows 10

Check Disk (CHKDSK) in Windows 10: Syntax, Parameters, Examples

Introduction Check Disk (CHKDSK) is a Windows 10 command tool that checks a disk for errors, fixes errors, recovers...
WSUS Offline Update Generator

How to Use WSUS Offline Update Tool to Patch Offline Computers

What is WSUS Offline Update? WSUS Offline Update is a free tool that allows you to download Microsoft updates....
NTFS Permissions with Share Permissions

NTFS Permissions with Share Permissions Compared

Introduction Combing NTFS permissions with share Permissions increases folder security within a network. Every Windows Administrator should have a...
Amazon Baby Registry

Amazon Baby Registry: Your Definitive Guide

Introduction Amazon Baby Registry allows expectant mothers to create a list of items they need for their baby. Friends...

Gmail Search by Date: How to Search Gmail by Date

Introduction You can search Gmail by date for emails sent during certain time period. You could also search Gmail...

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